﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GFMS.MODEL;
using KPIS.GERP.GFMS.IDAL;

namespace KPIS.GERP.GFMS.DAL
{
    [Serializable]
    public sealed class D_FIM_MoneyRequests : IDAL_FIM_MoneyRequests
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();

        public D_FIM_MoneyRequests()
        {

        }

        public IList<M_FIM_MoneyRequests> GetFull(Nullable<int> bgm_source_seq
            , Nullable<int> bgm_year, Nullable<int> work_area_seq, Nullable<int> event_seq
            , Nullable<int> contact_seq, Nullable<int> emp_seq, string record_status, bool bReadyToPay)
        {
            string strSQL;
            DataTable dtb = null;
            IList<M_FIM_MoneyRequests> infoList = new List<M_FIM_MoneyRequests>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(8);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@bgm_year", bgm_year);
                dbManager.AddParameters(3, "@work_area_seq", work_area_seq);
                dbManager.AddParameters(4, "@event_seq", event_seq);
                dbManager.AddParameters(5, "@contact_seq", contact_seq);
                dbManager.AddParameters(6, "@emp_seq", emp_seq);
                dbManager.AddParameters(7, "@record_status", record_status);
                
                strSQL = "SELECT mr.money_req_seq, mr.bgm_source_seq, mr.bgm_year, mr.bgm_exp_grp_seq, mr.work_area_seq"
                    + ", mr.event_seq, mr.money_req_date, mr.use_advance, mr.ref_no, mr.doc_ref_no, mr.money_req_name"
                    + ", mr.money_req_name_plus"
                    + ", mr.bgm_proj_oper_seq, mr.emp_seq, mr.job_site_seq, mr.contact_seq, mr.icm_dn_seq, mr.approver_seq"
                    + ", mr.travel_req_seq, mr.borrow_no, mr.borrow_type_seq"
                    + ", mr.borrow_start_date, mr.borrow_end_date, mr.borrow_return_date, mr.allow"
                    + ", mr.debtor_emp_seq, mr.debtor_contact_seq"
                    + ", mr.advance_bgm_source_seq, mr.transfer_type_seq"
                    + ", mr.credit_card_no"

                    + ", s.bgm_source_name, area.work_area_name, pj.bgm_proj_oper_code, pj.bgm_proj_oper_name, event.event_name"
                    + ", concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name) as emp_name"
                    //+ ", concat(approvenp.prefix_name, approve.first_name, ' ', approve.last_name) as approve_name"
                    + ", concat(debtornp.prefix_name, debtor.first_name, ' ', debtor.last_name) as debtor_emp_name"
                    //+ ", debtorcp.contact_person_name as debtor_contact_name"
                    //+ ", debtorcp.contact_seq as debtor_contact_seq"
                    + ", debtorc.NameTh as debtor_contact_name"
                    + ", c.NameTh"
                    + ", bt.borrow_type_name, mrd.total_amount"
                    + ", j.job_site_name, s.use_petition, pay.pay_seq"
                
                    + ", mr.approved_by, mr.approved_when, mr.tran_grp_seq"
                    + ", mr.remarks, mr.record_status, rs.record_status_name"
                    + ", mr.created_by, mr.created_when, mr.updated_by, mr.updated_when"
                    + " FROM fim_money_requests mr"
                    + " LEFT JOIN (select mrd.money_req_seq"
                        + ", sum(coalesce(mrd.amount, 0)) as total_amount" 
                        + " from fim_money_request_dtls mrd"
                        + " where mrd.record_status not like @delete_status"
                        + " group by mrd.money_req_seq"
                        + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
                    + " LEFT JOIN bgm_project_operation pj ON mr.bgm_proj_oper_seq = pj.bgm_proj_oper_seq"
                    + " LEFT JOIN fim_mas_events event ON mr.event_seq = event.event_seq"
                    + " LEFT JOIN bgm_mas_source s ON mr.bgm_source_seq = s.bgm_source_seq"
                    + " LEFT JOIN hrm_mp_work_areas area ON mr.work_area_seq = area.work_area_seq"
                    + " LEFT JOIN IcmMasContact c ON mr.contact_seq = c.ConId"
                    + " LEFT JOIN hrm_ps_employees emp ON mr.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes empnp ON emp.name_prefix_seq = empnp.name_prefix_seq"
                    + " LEFT JOIN hrm_mp_job_sites j ON mr.job_site_seq = j.job_site_seq"
                    + " LEFT JOIN fim_payments pay on mr.money_req_seq = pay.money_req_seq"
                        + " and pay.record_status not like 'D'"
                    //+ " LEFT JOIN hrm_ps_employees approve ON mr.approver_seq = approve.emp_seq"
                    //+ " LEFT JOIN mas_name_prefixes approvenp ON approve.name_prefix_seq = approvenp.name_prefix_seq"
                    + " LEFT JOIN hrm_ps_employees debtor ON mr.debtor_emp_seq = debtor.emp_seq"
                    + " LEFT JOIN mas_name_prefixes debtornp ON debtor.name_prefix_seq = debtornp.name_prefix_seq"
                    //+ " LEFT JOIN fim_mas_contact_persons debtorcp ON mr.debtor_contact_seq = debtorcp.contact_person_seq"
                    + " LEFT JOIN IcmMasContact debtorc ON mr.debtor_contact_seq = debtorc.ConId"
                    + " LEFT JOIN fim_mas_borrow_types bt ON mr.borrow_type_seq = bt.borrow_type_seq"
                    + " LEFT JOIN sys_record_status rs ON mr.record_status = rs.record_status"
                    + (record_status != null ?
                        (record_status == "W" ? " WHERE mr.record_status in ('P', 'W', 'R') and (mr.allow = '1' or mr.allow is null)"
                            : " WHERE mr.record_status in ('A') and (mr.allow = '1' or mr.allow is null)")
                        : " WHERE mr.record_status not like @delete_status")
                    + (bgm_source_seq != null ? " AND mr.bgm_source_seq = @bgm_source_seq" : "")
                    + (bgm_year != null ? " AND mr.bgm_year = @bgm_year" : "")
                    + (work_area_seq != null ? " AND mr.work_area_seq = @work_area_seq" : "")
                    + (event_seq != null ? " AND mr.event_seq = @event_seq" : "")
                    + (contact_seq != null ? " AND mr.contact_seq = @contact_seq" : "")
                    + (emp_seq != null ? " AND mr.emp_seq = @emp_seq" : "")
                    + " ORDER BY mr.money_req_date";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                if (dtb.Rows.Count > 0)
                {
                    for (int i = 0; i < dtb.Rows.Count; i++)
                    {
                        DataRow dr = dtb.Rows[i];
                        M_FIM_MoneyRequests info = new M_FIM_MoneyRequests();
                        info.money_req_seq = Convert.ToInt32(dr["money_req_seq"]);
                        info.bgm_source_seq = cn.ConvertNullInt(dr["bgm_source_seq"]);
                        info.bgm_year = cn.ConvertNullInt(dr["bgm_year"]);
                        info.bgm_exp_grp_seq = cn.ConvertNullInt(dr["bgm_exp_grp_seq"]);
                        info.work_area_seq = cn.ConvertNullInt(dr["work_area_seq"]);
                        info.event_seq = cn.ConvertNullInt(dr["event_seq"]);
                        info.money_req_date = cn.ConvertNullDateTime(dr["money_req_date"]);
                        info.use_advance = dr["use_advance"].ToString();
                        info.advance_bgm_source_seq = cn.ConvertNullInt(dr["advance_bgm_source_seq"]);
                        info.ref_no = dr["ref_no"].ToString();
                        info.doc_ref_no = dr["doc_ref_no"].ToString();
                        info.money_req_name = dr["money_req_name"].ToString();
                        info.money_req_name_plus = dr["money_req_name_plus"].ToString();
                        info.bgm_proj_oper_seq = cn.ConvertNullInt(dr["bgm_proj_oper_seq"]);
                        info.emp_seq = cn.ConvertNullInt(dr["emp_seq"]);
                        info.job_site_seq = cn.ConvertNullInt(dr["job_site_seq"]);
                        info.contact_seq = cn.ConvertNullInt(dr["contact_seq"]);
                        info.icm_dn_seq = cn.ConvertNullInt(dr["icm_dn_seq"]);
                        info.approver_seq = cn.ConvertNullInt(dr["approver_seq"]);
                        info.travel_req_seq = cn.ConvertNullInt(dr["travel_req_seq"]);
                        info.borrow_no = dr["borrow_no"].ToString();
                        info.borrow_type_seq = cn.ConvertNullInt(dr["borrow_type_seq"]);
                        info.borrow_start_date = cn.ConvertNullDateTime(dr["borrow_start_date"]);
                        info.borrow_end_date = cn.ConvertNullDateTime(dr["borrow_end_date"]);
                        info.borrow_return_date = cn.ConvertNullDateTime(dr["borrow_return_date"]);
                        info.debtor_emp_seq = cn.ConvertNullInt(dr["debtor_emp_seq"]);
                        info.debtor_contact_seq = cn.ConvertNullInt(dr["debtor_contact_seq"]);
                        info.transfer_type_seq = cn.ConvertNullInt(dr["transfer_type_seq"]);
                        info.credit_card_no = dr["credit_card_no"].ToString();

                        info.bgm_source_name = dr["bgm_source_name"].ToString();
                        info.work_area_name = dr["work_area_name"].ToString();
                        info.bgm_proj_oper_code = dr["bgm_proj_oper_code"].ToString();
                        info.bgm_proj_oper_name = dr["bgm_proj_oper_name"].ToString();
                        info.event_name = dr["event_name"].ToString();
                        info.emp_name = dr["emp_name"].ToString();
                        info.debtor_emp_name = dr["debtor_emp_name"].ToString();
                        info.debtor_contact_name = dr["debtor_contact_name"].ToString();
                        //info.debtor_contact_seq = cn.ConvertNullInt(dr["debtor_contact_seq"]);
                        //info.debtor_contact_name = dr["debtor_contact_name"].ToString();
                        info.contact_name = dr["NameTh"].ToString();
                        info.borrow_type_name = dr["borrow_type_name"].ToString();
                        info.total_amount = cn.ConvertNullDouble(dr["total_amount"]);
                        info.job_site_name = dr["job_site_name"].ToString();
                
                        info.approved_by = cn.ConvertNullInt(dr["approved_by"]);
                        info.approved_when = cn.ConvertNullDateTime(dr["approved_when"]);
                        info.tran_grp_seq = cn.ConvertNullInt(dr["tran_grp_seq"]);

                        info.record_status = dr["record_status"].ToString();
                        info.record_status_name = dr["record_status_name"].ToString();
                        info.created_by = cn.ConvertNullInt(dr["created_by"]);
                        info.created_when = cn.ConvertNullDateTime(dr["created_when"]);
                        info.updated_by = cn.ConvertNullInt(dr["updated_by"]);
                        info.updated_when = cn.ConvertNullDateTime(dr["updated_when"]);
                        //infoList.Add(info);

                        if (bReadyToPay)
                        {
                            if (info.record_status == "A" && cn.ConvertNullInt(dr["pay_seq"]) == null)
                            {
                                if (dr["use_petition"].ToString() == "1")
                                {
                                    if (info.use_advance == "0")
                                    {
                                        if (ReadyToPay(info.money_req_seq))
                                        {
                                            infoList.Add(info);
                                        }
                                    }
                                }
                                else
                                {
                                    infoList.Add(info);
                                }
                            }
                        }
                        else
                        {
                            infoList.Add(info);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
             
            return infoList;
        }

        public bool ReadyToPay(int money_req_seq)
        {
            bool bOK = false;
            string strSQL;
            try
            {
                //dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@money_req_seq", money_req_seq);

                strSQL = "select count(*) as row_count"
                    + " from fim_petition_dtls petd"
                    + " left join fim_receives r on petd.petition_seq = r.petition_seq"
                    + " left join fim_payments pay on petd.petition_dtls_seq = pay.petition_dtls_seq"
                        + " and pay.record_status not like @delete_status"
                    + " where r.record_status not like @delete_status"
                    + " and r.receive_seq is not null" 
                    + " and pay.pay_seq is null"
                    + " and petd.money_req_seq = @money_req_seq";

                DataRowCollection drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    bOK = Convert.ToInt32(drc[0]["row_count"]) > 0;
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                //dbManager.Dispose();
            }

            return bOK;
        }

        public IList<M_FIM_MoneyRequests> GetList(Nullable<int> bgm_source_seq
            , Nullable<int> bgm_year, Nullable<int> work_area_seq, Nullable<int> event_seq
            , Nullable<int> contact_seq, Nullable<int> emp_seq, string filter)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_MoneyRequests> infoList = new List<M_FIM_MoneyRequests>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(8);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@bgm_year", bgm_year);
                dbManager.AddParameters(3, "@work_area_seq", work_area_seq);
                dbManager.AddParameters(4, "@event_seq", event_seq);
                dbManager.AddParameters(5, "@contact_seq", contact_seq);
                dbManager.AddParameters(6, "@emp_seq", emp_seq);
                dbManager.AddParameters(7, "@filter", "%" + filter + "%");

                strSQL = "SELECT *"
                    + " FROM (SELECT mr.money_req_seq"
                        + ", CONCAT(coalesce(mr.borrow_no, ''), ' : ', coalesce(mr.money_req_name, '')) AS money_req_name2"
                        + ", CONCAT(coalesce(mr.ref_no, ''), ' : ', coalesce(mr.money_req_name, '')) AS money_req_name"
                        + " FROM fim_money_requests mr"
                        + " WHERE mr.record_status not like 'D'"
                        + (bgm_source_seq != null ? " AND mr.bgm_source_seq = @bgm_source_seq" : "")
                        + (bgm_year != null ? " AND mr.bgm_year = @bgm_year" : "")
                        + (work_area_seq != null ? " AND mr.work_area_seq = @work_area_seq" : "")
                        + (event_seq != null ? " AND mr.event_seq = @event_seq" : "")
                        + (contact_seq != null ? " AND mr.contact_seq = @contact_seq" : "")
                        + (emp_seq != null ? " AND mr.emp_seq = @emp_seq" : "")
                        + ") mr"
                    + (filter != null ? " WHERE mr.money_req_name like @filter" : "")
                    + " ORDER BY mr.money_req_name";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_MoneyRequests info = new M_FIM_MoneyRequests();
                        info.money_req_seq = Convert.ToInt32(drc[i]["money_req_seq"]);
                        info.money_req_name = drc[i]["money_req_name"].ToString();
                        info.money_req_name2 = drc[i]["money_req_name2"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_FIM_MoneyRequests> GetPetitionList(Nullable<int> bgm_source_seq
            , Nullable<int> bgm_year, Nullable<int> work_area_seq, Nullable<int> event_seq
            , Nullable<int> contact_seq, Nullable<int> emp_seq, string filter)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_MoneyRequests> infoList = new List<M_FIM_MoneyRequests>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(8);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@bgm_year", bgm_year);
                dbManager.AddParameters(3, "@work_area_seq", work_area_seq);
                dbManager.AddParameters(4, "@event_seq", event_seq);
                dbManager.AddParameters(5, "@contact_seq", contact_seq);
                dbManager.AddParameters(6, "@emp_seq", emp_seq);
                dbManager.AddParameters(7, "@filter", "%" + filter + "%");

                strSQL = "SELECT *"
                    + " FROM (SELECT mr.money_req_seq"
                        + ", CONCAT(coalesce(mr.ref_no, ''), ' : ', coalesce(mr.money_req_name, '')) AS money_req_name"
                        + " FROM fim_money_requests mr"
                        + " INNER JOIN fim_petition_dtls petd ON mr.money_req_seq = petd.money_req_seq"
                    //+ " INNER JOIN fim_petitions pet ON petd.petition_seq = pet.petition_seq"
                        + " INNER JOIN fim_receives r ON petd.petition_seq = r.petition_seq"
                        + " WHERE (mr.use_advance = '0' and mr.advance_bgm_source_seq is null)"
                        + (bgm_source_seq != null ? " AND mr.bgm_source_seq = @bgm_source_seq" : "")
                        + (bgm_year != null ? " AND mr.bgm_year = @bgm_year" : "")
                        + (work_area_seq != null ? " AND mr.work_area_seq = @work_area_seq" : "")
                        + (event_seq != null ? " AND mr.event_seq = @event_seq" : "")
                        + (contact_seq != null ? " AND mr.contact_seq = @contact_seq" : "")
                        + (emp_seq != null ? " AND mr.emp_seq = @emp_seq" : "")
                        + ") mr"
                    + (filter != null ? " WHERE mr.money_req_name like @filter" : "")

                    + " UNION ALL"

                    + " SELECT *"
                    + " FROM (SELECT mr.money_req_seq"
                        + ", CONCAT(coalesce(mr.ref_no, ''), ' : ', coalesce(mr.money_req_name, '')) AS money_req_name"
                        + " FROM fim_money_requests mr"
                        + " WHERE (mr.use_advance = '1' or mr.advance_bgm_source_seq is not null)"
                        + (bgm_source_seq != null ? " AND mr.bgm_source_seq = @bgm_source_seq" : "")
                        + (bgm_year != null ? " AND mr.bgm_year = @bgm_year" : "")
                        + (work_area_seq != null ? " AND mr.work_area_seq = @work_area_seq" : "")
                        + (event_seq != null ? " AND mr.event_seq = @event_seq" : "")
                        + (contact_seq != null ? " AND mr.contact_seq = @contact_seq" : "")
                        + (emp_seq != null ? " AND mr.emp_seq = @emp_seq" : "")
                        + ") mr"
                    + (filter != null ? " WHERE mr.money_req_name like @filter" : "")
                    ;
                    //+ " ORDER BY mr.money_req_name";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_MoneyRequests info = new M_FIM_MoneyRequests();
                        info.money_req_seq = Convert.ToInt32(drc[i]["money_req_seq"]);
                        info.money_req_name = drc[i]["money_req_name"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_FIM_MoneyRequests> GetNotPetition(Nullable<int> bgm_source_seq
            , Nullable<int> bgm_year, Nullable<int> petition_seq, Nullable<int> bgm_exp_grp_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_MoneyRequests> infoList = new List<M_FIM_MoneyRequests>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@bgm_year", bgm_year);
                dbManager.AddParameters(3, "@petition_seq", petition_seq);
                dbManager.AddParameters(4, "@bgm_exp_grp_seq", bgm_exp_grp_seq);

                strSQL = "SELECT mr.money_req_seq, mr.bgm_source_seq, mr.bgm_year, mr.bgm_exp_grp_seq, mr.work_area_seq"
                    + ", mr.event_seq, mr.money_req_date, mr.use_advance, mr.ref_no, mr.doc_ref_no, mr.money_req_name"
                    + ", mr.money_req_name_plus"
                    + ", mr.bgm_proj_oper_seq, mr.emp_seq, mr.job_site_seq, mr.contact_seq, mr.icm_dn_seq, mr.approver_seq"
                    + ", mr.travel_req_seq, mr.borrow_no, mr.borrow_type_seq"
                    + ", mr.borrow_start_date, mr.borrow_end_date, mr.borrow_return_date, mr.allow"
                    + ", mr.debtor_emp_seq, mr.debtor_contact_seq"
                    + ", mr.advance_bgm_source_seq"
                    //+ ", debtorcp.contact_seq as debtor_contact_seq"
                    + ", debtorc.NameTh as debtor_contact_name"
                    + ", mr.transfer_type_seq, mr.credit_card_no"

                    + ", s.bgm_source_name, area.work_area_name, pj.bgm_proj_oper_code, pj.bgm_proj_oper_name, event.event_name"
                    + ", concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name) as emp_name"
                    //+ ", concat(approvenp.prefix_name, approve.first_name, ' ', approve.last_name) as approve_name"
                    + ", concat(debtornp.prefix_name, debtor.first_name, ' ', debtor.last_name) as debtor_emp_name"
                    //+ ", debtorcp.contact_person_name as debtor_contact_name"
                    + ", debtorc.NameTh as debtor_contact_name"
                    + ", c.NameTh, bt.borrow_type_name, mrd.total_amount, mrd.total_amount_tax"
                    + ", pen.total_amount_penalty, dn.RedemptionAmount as total_amount_redemption, mrd.total_amount_fund"
                    + ", coalesce(mrd.total_amount, 0) - coalesce(mrd.total_amount_tax, 0)"
                        + " - coalesce(pen.total_amount_penalty, 0) - coalesce(dn.RedemptionAmount, 0)"
                        + " - coalesce(mrd.total_amount_fund, 0) as total_amount_real"
                    + ", petd.petition_seq, petd.petition_dtls_seq"
                    + ", pet.ref_no AS petition_ref_no"
                    + ", CASE WHEN pet.petition_type = '0' THEN 'ขอเบิกจากรายการรอเบิก'"
                        + " ELSE 'ขอเบิกงบอุดหนุน'"
                        + " END AS petition_type_name"
                    + @", CASE WHEN mr.debtor_contact_seq is not null THEN debtorc.NameTh 
    ELSE CASE WHEN mr.contact_seq is not null THEN c.NameTh
        ELSE concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name)
        END
    END AS payee_name"

                    + ", mr.approved_by, mr.approved_when, mr.tran_grp_seq"
                    + ", mr.remarks, mr.record_status, rs.record_status_name"
                    + ", mr.created_by, mr.created_when, mr.updated_by, mr.updated_when"
                    + " FROM fim_money_requests mr"
                    + " LEFT JOIN (select mrd.money_req_seq"
                        + ", sum(coalesce(mrd.amount, 0)) as total_amount"
                        + ", sum(coalesce(mrd.amount_tax, 0)) as total_amount_tax"
                        + ", sum(coalesce(mrd.amount_fund, 0)) as total_amount_fund"
                        + " from fim_money_request_dtls mrd"
                        + " where mrd.record_status not like 'D'"
                        + " group by mrd.money_req_seq"
                        + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
                    + " LEFT JOIN IcmDeliveryNote dn ON mr.icm_dn_seq = dn.DnId"
                    + " LEFT JOIN (select pen.DnPenId as DnId, sum(coalesce(pen.PenaltyFinalAmount, 0)) as total_amount_penalty"
                        + " from IcmDeliveryNotePenalty pen"
                        + " group by pen.DnPenId"
                        + ") pen ON mr.icm_dn_seq = pen.DnId"
                    + " LEFT JOIN bgm_project_operation pj ON mr.bgm_proj_oper_seq = pj.bgm_proj_oper_seq"
                    + " LEFT JOIN fim_mas_events event ON mr.event_seq = event.event_seq"
                    + " LEFT JOIN bgm_mas_source s ON mr.bgm_source_seq = s.bgm_source_seq"
                    + " LEFT JOIN hrm_mp_work_areas area ON mr.work_area_seq = area.work_area_seq"
                    + " LEFT JOIN IcmMasContact c ON mr.contact_seq = c.ConId"
                    + " LEFT JOIN hrm_ps_employees emp ON mr.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes empnp ON emp.name_prefix_seq = empnp.name_prefix_seq"
                    + " LEFT JOIN hrm_mp_job_sites j ON mr.job_site_seq = j.job_site_seq"
                    //+ " LEFT JOIN hrm_ps_employees approve ON mr.approver_seq = approve.emp_seq"
                    //+ " LEFT JOIN mas_name_prefixes approvenp ON approve.name_prefix_seq = approvenp.name_prefix_seq"
                    + " LEFT JOIN hrm_ps_employees debtor ON mr.debtor_emp_seq = debtor.emp_seq"
                    + " LEFT JOIN mas_name_prefixes debtornp ON debtor.name_prefix_seq = debtornp.name_prefix_seq"
                    //+ " LEFT JOIN fim_mas_contact_persons debtorcp ON mr.debtor_contact_seq = debtorcp.contact_person_seq"
                    + " LEFT JOIN IcmMasContact debtorc ON mr.debtor_contact_seq = debtorc.ConId"
                    + " LEFT JOIN fim_mas_borrow_types bt ON mr.borrow_type_seq = bt.borrow_type_seq"
                    + " LEFT JOIN fim_petition_dtls petd ON mr.money_req_seq = petd.money_req_seq and pet.record_status <> 'D'"
                    + " LEFT JOIN fim_petitions pet ON petd.petition_seq = pet.petition_seq and pet.record_status <> 'D'"

                    //+ @" LEFT JOIN 
//"

                    + " LEFT JOIN sys_record_status rs ON mr.record_status = rs.record_status"
                    + " WHERE mr.record_status = 'A'"
                    + (petition_seq != null ? " AND (petd.petition_seq is null OR petd.petition_seq = @petition_seq)" : " AND petd.petition_seq is null")
                    //+ " AND petd.petition_dtls_seq is null"
                    + (bgm_source_seq != null ? " AND mr.bgm_source_seq = @bgm_source_seq" : "")
                    + (bgm_year != null ? " AND mr.bgm_year = @bgm_year" : "")
                    + (bgm_exp_grp_seq != null ? " AND mr.bgm_exp_grp_seq = @bgm_exp_grp_seq" : "")
                    + " ORDER BY mr.money_req_date";
                

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_MoneyRequests info = new M_FIM_MoneyRequests();
                        info.money_req_seq = Convert.ToInt32(drc[i]["money_req_seq"]);
                        info.bgm_source_seq = cn.ConvertNullInt(drc[i]["bgm_source_seq"]);
                        info.bgm_year = cn.ConvertNullInt(drc[i]["bgm_year"]);
                        info.bgm_exp_grp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_grp_seq"]);
                        info.work_area_seq = cn.ConvertNullInt(drc[i]["work_area_seq"]);
                        info.event_seq = cn.ConvertNullInt(drc[i]["event_seq"]);
                        info.money_req_date = cn.ConvertNullDateTime(drc[i]["money_req_date"]);
                        info.use_advance = drc[i]["use_advance"].ToString();
                        info.advance_bgm_source_seq = cn.ConvertNullInt(drc[i]["advance_bgm_source_seq"]);
                        info.ref_no = drc[i]["ref_no"].ToString();
                        info.doc_ref_no = drc[i]["doc_ref_no"].ToString();
                        info.money_req_name = drc[i]["money_req_name"].ToString();
                        info.money_req_name_plus = drc[i]["money_req_name_plus"].ToString();
                        info.bgm_proj_oper_seq = cn.ConvertNullInt(drc[i]["bgm_proj_oper_seq"]);
                        info.emp_seq = cn.ConvertNullInt(drc[i]["emp_seq"]);
                        info.job_site_seq = cn.ConvertNullInt(drc[i]["job_site_seq"]);
                        info.contact_seq = cn.ConvertNullInt(drc[i]["contact_seq"]);
                        info.icm_dn_seq = cn.ConvertNullInt(drc[i]["icm_dn_seq"]);
                        info.approver_seq = cn.ConvertNullInt(drc[i]["approver_seq"]);
                        info.travel_req_seq = cn.ConvertNullInt(drc[i]["travel_req_seq"]);
                        info.borrow_no = drc[i]["borrow_no"].ToString();
                        info.borrow_type_seq = cn.ConvertNullInt(drc[i]["borrow_type_seq"]);
                        info.borrow_start_date = cn.ConvertNullDateTime(drc[i]["borrow_start_date"]);
                        info.borrow_end_date = cn.ConvertNullDateTime(drc[i]["borrow_end_date"]);
                        info.borrow_return_date = cn.ConvertNullDateTime(drc[i]["borrow_return_date"]);
                        info.debtor_emp_seq = cn.ConvertNullInt(drc[i]["debtor_emp_seq"]);
                        info.debtor_contact_seq = cn.ConvertNullInt(drc[i]["debtor_contact_seq"]);
                        info.transfer_type_seq = cn.ConvertNullInt(drc[i]["transfer_type_seq"]);
                        info.credit_card_no = drc[i]["credit_card_no"].ToString();
                        info.payee_name = drc[i]["payee_name"].ToString();

                        info.bgm_source_name = drc[i]["bgm_source_name"].ToString();
                        info.work_area_name = drc[i]["work_area_name"].ToString();
                        info.bgm_proj_oper_code = drc[i]["bgm_proj_oper_code"].ToString();
                        info.bgm_proj_oper_name = drc[i]["bgm_proj_oper_name"].ToString();
                        info.event_name = drc[i]["event_name"].ToString();
                        info.emp_name = drc[i]["emp_name"].ToString();
                        info.debtor_emp_name = drc[i]["debtor_emp_name"].ToString();
                        info.debtor_contact_name = drc[i]["debtor_contact_name"].ToString();
                        //info.debtor_contact_seq = cn.ConvertNullInt(drc[i]["debtor_contact_seq"]);
                        //info.debtor_contact_name = drc[i]["debtor_contact_name"].ToString();
                        info.contact_name = drc[i]["NameTh"].ToString();
                        info.borrow_type_name = drc[i]["borrow_type_name"].ToString();
                        info.total_amount = cn.ConvertNullDouble(drc[i]["total_amount"]);
                        info.total_amount_tax = cn.ConvertNullDouble(drc[i]["total_amount_tax"]);
                        info.total_amount_penalty = cn.ConvertNullDouble(drc[i]["total_amount_penalty"]);
                        info.total_amount_redemption = cn.ConvertNullDouble(drc[i]["total_amount_redemption"]);
                        info.total_amount_real = cn.ConvertNullDouble(drc[i]["total_amount_real"]);
                        info.petition_seq = cn.ConvertNullInt(drc[i]["petition_seq"]);
                        info.petition_dtls_seq = cn.ConvertNullInt(drc[i]["petition_dtls_seq"]);

                        info.approved_by = cn.ConvertNullInt(drc[i]["approved_by"]);
                        info.approved_when = cn.ConvertNullDateTime(drc[i]["approved_when"]);
                        info.tran_grp_seq = cn.ConvertNullInt(drc[i]["tran_grp_seq"]);

                        info.record_status = drc[i]["record_status"].ToString();
                        info.record_status_name = drc[i]["record_status_name"].ToString();
                        info.created_by = cn.ConvertNullInt(drc[i]["created_by"]);
                        info.created_when = cn.ConvertNullDateTime(drc[i]["created_when"]);
                        info.updated_by = cn.ConvertNullInt(drc[i]["updated_by"]);
                        info.updated_when = cn.ConvertNullDateTime(drc[i]["updated_when"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_FIM_MoneyRequests> GetNotReturnList(Nullable<int> return_seq
            , int? emp_seq, Nullable<int> contact_person_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_MoneyRequests> infoList = new List<M_FIM_MoneyRequests>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@return_seq", return_seq);
                dbManager.AddParameters(2, "@emp_seq", emp_seq);
                dbManager.AddParameters(3, "@contact_person_seq", contact_person_seq);

                strSQL = "SELECT mr.money_req_seq, concat(coalesce(mr.borrow_no, ''), ' : ', coalesce(mr.money_req_name, '')) as money_req_name"
                    + ", mr.borrow_no, mr.borrow_return_date, mr.credit_card_no"
                    + ", coalesce(mrd.req_amount, 0) as req_amount"
                    + ", coalesce(red.return_amount, 0) as return_amount"
                    + ", coalesce(r.receive_amount, 0) as receive_amount"
                    + ", mr.remarks"
                    + " FROM fim_money_requests mr"
                    + " LEFT JOIN (select mrd.money_req_seq"
                        + ", sum(coalesce(mrd.amount, 0)) as req_amount"
                        + " from fim_money_request_dtls mrd"
                        + " where mrd.record_status not like @delete_status"
                        + " group by mrd.money_req_seq"
                        + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
                    + " LEFT JOIN (select re.money_req_seq"
                        + ", sum(coalesce(red.amount, 0)) as return_amount"
                        + " from fim_money_returns re"
                        + " left join fim_money_return_dtls red ON re.return_seq = red.return_seq"
                            + " and red.record_status not like @delete_status"
                        + " WHERE re.record_status not like @delete_status"
                        + (return_seq != null ? " and re.return_seq not like @return_seq" : "")
                        + " group by re.money_req_seq"
                        + ") red ON mr.money_req_seq = red.money_req_seq"
                    + " LEFT JOIN (select r.money_req_seq"
                        + ", sum(coalesce(r.amount, 0)) as receive_amount"
                        + " from fim_receives r"
                        + " WHERE r.record_status not like @delete_status"
                        + " group by r.money_req_seq"
                        + ") r ON mr.money_req_seq = r.money_req_seq"

                    + " WHERE mr.record_status = 'A'"
                    + " AND mrd.req_amount > coalesce(red.return_amount, 0) + coalesce(r.receive_amount, 0)"
                    + (emp_seq != null ? " AND mr.debtor_emp_seq = @emp_seq" : "")
                    + (contact_person_seq != null ? " AND mr.debtor_contact_seq = @contact_person_seq" : "");
                    //+ " ORDER BY mr.money_req_date";
                

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_MoneyRequests info = new M_FIM_MoneyRequests();
                        info.money_req_seq = Convert.ToInt32(drc[i]["money_req_seq"]);
                        info.money_req_name = drc[i]["money_req_name"].ToString();
                        info.borrow_no = drc[i]["borrow_no"].ToString();
                        info.credit_card_no = drc[i]["credit_card_no"].ToString();
                        info.borrow_return_date = cn.ConvertNullDateTime(drc[i]["borrow_return_date"]);
                        info.total_amount = cn.ConvertNullDouble(drc[i]["req_amount"]);
                        info.return_amount = cn.ConvertNullDouble(drc[i]["return_amount"]);
                        info.receive_amount = cn.ConvertNullDouble(drc[i]["receive_amount"]);
                        info.remain_amount = (cn.ConvertNullDouble(drc[i]["req_amount"]) ?? 0)
                            - (cn.ConvertNullDouble(drc[i]["return_amount"]) ?? 0)
                            - (cn.ConvertNullDouble(drc[i]["receive_amount"]) ?? 0);
                        info.remarks = drc[i]["remarks"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public M_FIM_MoneyRequests GetNotReturnInfo(Nullable<int> money_req_seq, Nullable<int> return_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_FIM_MoneyRequests info = new M_FIM_MoneyRequests();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@money_req_seq", money_req_seq);
                dbManager.AddParameters(2, "@return_seq", return_seq);

                strSQL = "SELECT mr.money_req_seq, concat(mr.borrow_no, ' : ', mr.money_req_name) as money_req_name"
                    + ", mr.borrow_type_seq"
                    + ", coalesce(mrd.req_amount, 0) as req_amount"
                    + ", coalesce(red.return_amount, 0) as return_amount"
                    + ", coalesce(r.receive_amount, 0) as receive_amount"
                    + " FROM fim_money_requests mr"
                    + " LEFT JOIN (select mrd.money_req_seq"
                        + ", sum(coalesce(mrd.bath_amount, 0)) as req_amount"
                        + " from fim_money_request_dtls mrd"
                        + " where mrd.record_status not like 'D'"
                        + " group by mrd.money_req_seq"
                        + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
                    + " LEFT JOIN (select re.money_req_seq"
                        + ", sum(coalesce(red.bath_amount, 0)) as return_amount"
                        + " from fim_money_returns re"
                        + " left join fim_money_return_dtls red ON re.return_seq = red.return_seq"
                            + " and red.record_status not like 'D'"
                        + " WHERE re.record_status not like 'D'"
                        + (return_seq != null ? " and re.return_seq not like @return_seq" : "")
                        + " group by re.money_req_seq"
                        + ") red ON mr.money_req_seq = red.money_req_seq"
                    + " LEFT JOIN (select r.money_req_seq"
                        + ", sum(coalesce(r.bath_amount, 0)) as receive_amount"
                        + " from fim_receives r"
                        + " WHERE r.record_status = 'A'"
                        + " group by r.money_req_seq"
                        + ") r ON mr.money_req_seq = r.money_req_seq"

                    + " WHERE mr.money_req_seq = @money_req_seq";
                

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.money_req_seq = Convert.ToInt32(drc[0]["money_req_seq"]);
                    info.money_req_name = drc[0]["money_req_name"].ToString();
                    info.borrow_type_seq = cn.ConvertNullInt(drc[0]["borrow_type_seq"]);
                    info.total_amount = cn.ConvertNullDouble(drc[0]["req_amount"]);
                    info.return_amount = cn.ConvertNullDouble(drc[0]["return_amount"]);
                    info.receive_amount = cn.ConvertNullDouble(drc[0]["receive_amount"]);
                    info.remain_amount = (cn.ConvertNullDouble(drc[0]["req_amount"]) ?? 0)
                        - (cn.ConvertNullDouble(drc[0]["return_amount"]) ?? 0)
                        - (cn.ConvertNullDouble(drc[0]["receive_amount"]) ?? 0);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public DataTable GetNotPayment(Nullable<int> bgm_year, Nullable<int> bgm_source_seq
            , Nullable<int> work_area_seq, Nullable<int> event_seq)
        {
            string strSQL;
            DataTable dtb = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@record_status", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(1, "@bgm_year", bgm_year);
                dbManager.AddParameters(2, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(3, "@work_area_seq", work_area_seq);
                dbManager.AddParameters(4, "@event_seq", event_seq);

                strSQL = "SELECT mr.money_req_seq"
                    + ", mr.money_req_name, mr.money_req_date, mr.ref_no"
                    + ", CASE WHEN COALESCE(mr.debtor_contact_seq, '') not like ''"
                        + " THEN debtorc.NameTh"
                        + " ELSE CASE WHEN COALESCE(mr.emp_seq, '') not like ''"
                            + " THEN concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name)"
                            + " ELSE c.NameTh"
                            + " END"
                        + " END AS payee_name"

                    + ", mrd.total_amount, mrd.total_amount_tax"
                    + ", pen.total_amount_penalty, dn.RedemptionAmount as total_amount_redemption, mrd.total_amount_fund"
                    + ", coalesce(mrd.total_amount, 0) - coalesce(mrd.total_amount_tax, 0)"
                        + " - coalesce(pen.total_amount_penalty, 0) - coalesce(dn.RedemptionAmount, 0)"
                        + " - coalesce(mrd.total_amount_fund, 0) as total_amount_real"
                    + " FROM fim_money_requests mr"
                    + " LEFT JOIN (select mrd.money_req_seq"
                        + ", sum(coalesce(mrd.bath_amount, 0)) as total_amount"
                        + ", sum(coalesce(mrd.bath_amount_tax, 0)) as total_amount_tax"
                        + ", sum(coalesce(mrd.bath_amount_fund, 0)) as total_amount_fund"
                        + " from fim_money_request_dtls mrd"
                        + " where mrd.record_status not like 'D'"
                        + " group by mrd.money_req_seq"
                        + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
                    + " LEFT JOIN IcmDeliveryNote dn ON mr.icm_dn_seq = dn.DnId"
                    + " LEFT JOIN (select pen.DnPenId as DnId, sum(coalesce(pen.PenaltyFinalAmount, 0)) as total_amount_penalty"
                        + " from IcmDeliveryNotePenalty pen"
                        + " group by pen.DnId"
                        + ") pen ON mr.icm_dn_seq = pen.DnId"
                    + " LEFT JOIN bgm_project_operation pj ON mr.bgm_proj_oper_seq = pj.bgm_proj_oper_seq"
                    + " LEFT JOIN fim_mas_events event ON mr.event_seq = event.event_seq"
                    + " LEFT JOIN bgm_mas_source s ON mr.bgm_source_seq = s.bgm_source_seq"
                    + " LEFT JOIN hrm_mp_work_areas area ON mr.work_area_seq = area.work_area_seq"
                    + " LEFT JOIN IcmMasContact c ON mr.contact_seq = c.ConId"
                    + " LEFT JOIN hrm_ps_employees emp ON mr.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes empnp ON emp.name_prefix_seq = empnp.name_prefix_seq"
                    + " LEFT JOIN hrm_mp_job_sites j ON mr.job_site_seq = j.job_site_seq"
                    + " LEFT JOIN hrm_ps_employees debtor ON mr.debtor_emp_seq = debtor.emp_seq"
                    + " LEFT JOIN mas_name_prefixes debtornp ON debtor.name_prefix_seq = debtornp.name_prefix_seq"
                    //+ " LEFT JOIN fim_mas_contact_persons debtorcp ON mr.debtor_contact_seq = debtorcp.contact_person_seq"
                    + " LEFT JOIN IcmMasContact debtorc ON mr.debtor_contact_seq = debtorc.ConId"
                    + " LEFT JOIN fim_mas_borrow_types bt ON mr.borrow_type_seq = bt.borrow_type_seq"
                    + " LEFT JOIN fim_petition_dtls petd ON mr.money_req_seq = petd.money_req_seq"
                    + " LEFT JOIN fim_petitions pet ON petd.petition_seq = pet.petition_seq"
                    + " LEFT JOIN sys_record_status rs ON mr.record_status = rs.record_status"

                    + " WHERE mr.record_status = @record_status"
                    + (bgm_year != null ? " AND mr.bgm_year = @bgm_year" : "")
                    + (bgm_source_seq != null ? " AND mr.bgm_source_seq = @bgm_source_seq" : "")
                    + (work_area_seq != null ? " AND mr.work_area_seq = @work_area_seq" : "")
                    + (event_seq != null ? " AND mr.event_seq = @event_seq" : "");
                

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public M_FIM_MoneyRequests GetDnDeductInfo(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_FIM_MoneyRequests info = new M_FIM_MoneyRequests();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT mr.money_req_seq, mrd.total_amount"
                    + ", mrd.total_amount_tax, pen.total_amount_penalty"
                    + ", dn.RedemptionAmount as total_amount_redemption"
                    + ", dn.DeductionAmount as total_amount_deduction"
                    + ", coalesce(mrd.total_amount, 0) - coalesce(mrd.TotalAmountTax, 0) - coalesce(pen.TotalAmountPenalty, 0)"
                        + " - coalesce(dn.RedemptionAmount, 0) - coalesce(dn.DeductionAmount, 0) as total_amount_real"
                    + " FROM fim_money_requests mr"
                    + " LEFT JOIN (select mrd.money_req_seq"
                        + ", sum(coalesce(mrd.bath_amount, 0)) as total_amount"
                        + ", sum(coalesce(mrd.bath_amount_tax, 0)) as total_amount_tax"
                        + " from fim_money_request_dtls mrd"
                        + " where mrd.record_status not like @delete_status"
                        + " group by mrd.money_req_seq"
                        + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
                    + " LEFT JOIN IcmDeliveryNote dn ON mr.icm_dn_seq = dn.DnId"
                    + " LEFT JOIN (select pen.DnPenId as DnId"
                        + ", sum(coalesce(pen.PenaltyFinalAmount, 0)) as total_amount_penalty"
                        + " from IcmDeliveryNotePenalty pen"
                        + " group by pen.DnId"
                        + ") pen ON mr.icm_dn_seq = pen.DnId"
                    + " WHERE mr.money_req_seq = @seq";
                

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.money_req_seq = Convert.ToInt32(drc[0]["money_req_seq"]);
                    info.total_amount = cn.ConvertNullDouble(drc[0]["total_amount"]);
                    info.total_amount_tax = cn.ConvertNullDouble(drc[0]["total_amount_tax"]);
                    info.total_amount_penalty = cn.ConvertNullDouble(drc[0]["total_penalty_amount"]);
                    info.total_amount_redemption = cn.ConvertNullDouble(drc[0]["total_amount_redemption"]);
                    info.total_amount_deduction = cn.ConvertNullDouble(drc[0]["total_amount_redemption"]);
                    info.total_amount_real = cn.ConvertNullDouble(drc[0]["total_amount_real"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public IList<M_HRM_PR_PayrollDtls> GetSalaryDeductList(int money_req_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_HRM_PR_PayrollDtls> infoList = new List<M_HRM_PR_PayrollDtls>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@money_req_seq", money_req_seq);
                dbManager.AddParameters(1, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT prd.ext_seq, ext.ext_name"
                    + ", sum(coalesce(prd.amount_pay, 0)) as amount_pay"

                    + " FROM hrm_pr_payroll_dtls prd"
                    + " LEFT JOIN hrm_pr_extra_types ext ON prd.ext_seq = ext.ext_seq"
                    + " LEFT JOIN fim_money_request_dtls mrd ON prd.money_req_dtls_seq = mrd.money_req_dtls_seq"
                    + " WHERE mrd.money_req_seq = @money_req_seq"
                    + " AND ext.ext_flag = 'D'"
                    + " GROUP BY prd.ext_seq, ext.ext_name";
                

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_HRM_PR_PayrollDtls info = new M_HRM_PR_PayrollDtls();
                        //info.money_req_dtls_seq = cn.ConvertNullInt(drc[i]["money_req_dtls_seq"]);
                        info.pay_dtls_seq = Convert.ToInt32(drc[i]["pay_dtls_seq"]);
                        info.ext_seq = cn.ConvertNullInt(drc[i]["ext_seq"]);
                        info.amount_pay = cn.ConvertNullDouble(drc[i]["amount_pay"]);
                        info.ext_name = drc[i]["ext_name"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public M_FIM_MoneyRequests GetInfo(int seq)
        {
            return GetInfo(seq, null);
        }

        public M_FIM_MoneyRequests GetInfo(int seq, int? receive_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_FIM_MoneyRequests info = new M_FIM_MoneyRequests();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@receive_seq", receive_seq);
                dbManager.AddParameters(2, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT mr.money_req_seq, mr.bgm_source_seq, mr.bgm_year, mr.bgm_exp_grp_seq, mr.work_area_seq"
                    + ", mr.event_seq, mr.money_req_date, mr.use_advance, mr.ref_no, mr.doc_ref_no, mr.money_req_name"
                    + ", mr.money_req_name_plus, mr.bidder_col_Id"
                    + ", mr.bgm_proj_oper_seq, mr.emp_seq, mr.job_site_seq, mr.contact_seq, mr.icm_dn_seq, mr.approver_seq"
                    + ", mr.travel_req_seq, mr.borrow_no, mr.borrow_type_seq"
                    + ", mr.borrow_start_date, mr.borrow_end_date, mr.borrow_return_date, mr.allow"
                    + ", mr.debtor_emp_seq, mr.debtor_contact_seq"
                    + ", mr.advance_bgm_source_seq, expgrp.bgm_exp_grp_name"
                    //+ ", debtorcp.contact_seq as debtor_contact_seq"
                    + ", debtorc.NameTh as debtor_contact_name"
                    + ", mr.transfer_type_seq, mr.credit_card_no"

                    + ", s.bgm_source_name, area.work_area_name, pj.bgm_proj_oper_code, pj.bgm_proj_oper_name, event.event_name"
                    + ", concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name) as emp_name"
                    //+ ", concat(approvenp.prefix_name, approve.first_name, ' ', approve.last_name) as approve_name"
                    + ", concat(debtornp.prefix_name, debtor.first_name, ' ', debtor.last_name) as debtor_emp_name"
                    //+ ", debtorcp.contact_person_name as debtor_contact_name"
                    + ", debtorc.NameTh as debtor_contact_name"
                    + ", c.NameTh, bt.borrow_type_name"
                    + ", coalesce(mrd.total_amount, 0) as total_amount"
                    + ", coalesce(mrd.total_amount_tax, 0) as total_amount_tax"
                    + ", coalesce(pen.total_amount_penalty, 0) as total_amount_penalty"
                    + ", coalesce(dn.RedemptionAmount, 0) as total_amount_redemption"
                    + ", coalesce(dn.DeductionAmount, 0) as total_amount_deduction"
                    + ", coalesce(mrd.total_amount, 0) - coalesce(mrd.total_amount_tax, 0) - coalesce(pen.total_amount_penalty, 0)"
                        + " - coalesce(dn.RedemptionAmount, 0) - coalesce(dn.DeductionAmount, 0) as total_amount_real"
                    + ", coalesce(mrd.percent_vat, 0) as percent_vat"
                    + ", coalesce(mrd.percent_tax, 0) as percent_tax"
                    + ", petd.petition_seq, petd.petition_dtls_seq"
                    + ", pet.ref_no AS petition_ref_no, pet.petition_no"
                    + ", CASE WHEN pet.petition_type = '0' THEN 'ขอเบิกจากรายการรอเบิก'"
                        + " ELSE 'ขอเบิกงบอุดหนุน'"
                        + " END AS petition_type_name"
                    + ", CASE WHEN COALESCE(mr.debtor_contact_seq, 0) not like 0"
                        + " THEN debtorc.NameTh"
                        + " ELSE CASE WHEN COALESCE(mr.debtor_emp_seq, 0) not like 0"
                            + " THEN concat(debtornp.prefix_name, debtor.first_name, ' ', debtor.last_name)"
                            + " ELSE CASE WHEN COALESCE(mr.contact_seq, 0) not like 0"
                                + " THEN c.NameTh"
                                + " ELSE concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name)"
                                + " END"
                            + " END"
                        + " END AS payee_name"
                    + ", mr.transfer_job_site_seq, j2.job_site_name as transfer_job_site_name"
                    + ", coalesce(red.return_amount, 0) as return_amount"
                    + ", coalesce(r.receive_amount, 0) as receive_amount"

                    + ", mr.approved_by, mr.approved_when, mr.tran_grp_seq"
                    + ", mr.remarks, mr.record_status, rs.record_status_name"
                    + ", mr.created_by, mr.created_when, mr.updated_by, mr.updated_when"
                    + " FROM fim_money_requests mr"
                    + " LEFT JOIN (select mrd.money_req_seq"
                        + ", max(coalesce(mrd.percent_vat, 0)) as percent_vat, max(coalesce(mrd.percent_tax, 0)) as percent_tax"
                        + ", sum(coalesce(mrd.amount, 0)) as total_amount"
                        + ", sum(coalesce(mrd.amount_tax, 0)) as total_amount_tax"
                        + " from fim_money_request_dtls mrd"
                        + " where mrd.record_status not like @delete_status"
                        + " group by mrd.money_req_seq"//, mrd.percent_vat, mrd.percent_tax
                        + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
                    + " LEFT JOIN bgm_project_operation pj ON mr.bgm_proj_oper_seq = pj.bgm_proj_oper_seq"
                    + " LEFT JOIN fim_mas_events event ON mr.event_seq = event.event_seq"
                    + " LEFT JOIN bgm_mas_source s ON mr.bgm_source_seq = s.bgm_source_seq"
                    + " LEFT JOIN hrm_mp_work_areas area ON mr.work_area_seq = area.work_area_seq"
                    + " LEFT JOIN IcmMasContact c ON mr.contact_seq = c.ConId"
                    + " LEFT JOIN hrm_ps_employees emp ON mr.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes empnp ON emp.name_prefix_seq = empnp.name_prefix_seq"
                    + " LEFT JOIN hrm_mp_job_sites j ON mr.job_site_seq = j.job_site_seq"
                    + " LEFT JOIN hrm_mp_job_sites j2 ON mr.transfer_job_site_seq = j2.job_site_seq"
                    //+ " LEFT JOIN hrm_ps_employees approve ON mr.approver_seq = approve.emp_seq"
                    //+ " LEFT JOIN mas_name_prefixes approvenp ON approve.name_prefix_seq = approvenp.name_prefix_seq"
                    + " LEFT JOIN hrm_ps_employees debtor ON mr.debtor_emp_seq = debtor.emp_seq"
                    + " LEFT JOIN mas_name_prefixes debtornp ON debtor.name_prefix_seq = debtornp.name_prefix_seq"
                    //+ " LEFT JOIN fim_mas_contact_persons debtorcp ON mr.debtor_contact_seq = debtorcp.contact_person_seq"
                    + " LEFT JOIN IcmMasContact debtorc ON mr.debtor_contact_seq = debtorc.ConId"
                    + " LEFT JOIN fim_mas_borrow_types bt ON mr.borrow_type_seq = bt.borrow_type_seq"
                    + " LEFT JOIN fim_petition_dtls petd ON mr.money_req_seq = petd.money_req_seq"
                    + " LEFT JOIN fim_petitions pet ON petd.petition_seq = pet.petition_seq"
                    + " LEFT JOIN IcmDeliveryNote dn ON mr.icm_dn_seq = dn.DnId"
                    + " LEFT JOIN bgm_mas_expense_group expgrp on mr.bgm_exp_grp_seq = expgrp.bgm_exp_grp_seq"
                    + " LEFT JOIN (select pen.DnId DnId"
                        + ", sum(coalesce(pen.PenaltyFinalAmount, 0)) as total_amount_penalty"
                        + " from IcmDeliveryNotePenalty pen"
                        + " group by pen.DnId"
                        + ") pen ON mr.icm_dn_seq = pen.DnId"
                    + " LEFT JOIN (select re.money_req_seq"
                        + ", sum(coalesce(red.amount, 0)) as return_amount"
                        + " from fim_money_returns re"
                        + " left join fim_money_return_dtls red ON re.return_seq = red.return_seq"
                            + " and red.record_status not like 'D'"
                        + " WHERE re.record_status not like 'D'"
                        + " group by re.money_req_seq"
                        + ") red ON mr.money_req_seq = red.money_req_seq"
                    + " LEFT JOIN (select r.money_req_seq"
                        + ", sum(coalesce(r.amount, 0)) as receive_amount"
                        + " from fim_receives r"
                        + " WHERE r.record_status = 'A'"
                        + (receive_seq != null ? " and r.receive_seq <> @receive_seq" : "")
                        + " group by r.money_req_seq"
                        + ") r ON mr.money_req_seq = r.money_req_seq"
                    + " LEFT JOIN sys_record_status rs ON mr.record_status = rs.record_status"
                    + " WHERE mr.money_req_seq = @seq";
                
                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.money_req_seq = Convert.ToInt32(drc[0]["money_req_seq"]);
                    info.bgm_source_seq = cn.ConvertNullInt(drc[0]["bgm_source_seq"]);
                    info.bgm_year = cn.ConvertNullInt(drc[0]["bgm_year"]);
                    info.bgm_exp_grp_seq = cn.ConvertNullInt(drc[0]["bgm_exp_grp_seq"]);
                    info.work_area_seq = cn.ConvertNullInt(drc[0]["work_area_seq"]);
                    info.event_seq = cn.ConvertNullInt(drc[0]["event_seq"]);
                    info.money_req_date = cn.ConvertNullDateTime(drc[0]["money_req_date"]);
                    info.use_advance = drc[0]["use_advance"].ToString();
                    info.advance_bgm_source_seq = cn.ConvertNullInt(drc[0]["advance_bgm_source_seq"]);
                    info.ref_no = drc[0]["ref_no"].ToString();
                    info.doc_ref_no = drc[0]["doc_ref_no"].ToString();
                    info.money_req_name = drc[0]["money_req_name"].ToString();
                    info.money_req_name_plus = drc[0]["money_req_name_plus"].ToString();
                    info.bgm_proj_oper_seq = cn.ConvertNullInt(drc[0]["bgm_proj_oper_seq"]);
                    info.emp_seq = cn.ConvertNullInt(drc[0]["emp_seq"]);
                    info.job_site_seq = cn.ConvertNullInt(drc[0]["job_site_seq"]);
                    info.contact_seq = cn.ConvertNullInt(drc[0]["contact_seq"]);
                    info.icm_dn_seq = cn.ConvertNullInt(drc[0]["icm_dn_seq"]);
                    info.approver_seq = cn.ConvertNullInt(drc[0]["approver_seq"]);
                    info.travel_req_seq = cn.ConvertNullInt(drc[0]["travel_req_seq"]);
                    info.borrow_no = drc[0]["borrow_no"].ToString();
                    info.borrow_type_seq = cn.ConvertNullInt(drc[0]["borrow_type_seq"]);
                    info.borrow_start_date = cn.ConvertNullDateTime(drc[0]["borrow_start_date"]);
                    info.borrow_end_date = cn.ConvertNullDateTime(drc[0]["borrow_end_date"]);
                    info.borrow_return_date = cn.ConvertNullDateTime(drc[0]["borrow_return_date"]);
                    info.debtor_emp_seq = cn.ConvertNullInt(drc[0]["debtor_emp_seq"]);
                    info.debtor_contact_seq = cn.ConvertNullInt(drc[0]["debtor_contact_seq"]);
                    info.petition_seq = cn.ConvertNullInt(drc[0]["petition_seq"]);
                    info.petition_dtls_seq = cn.ConvertNullInt(drc[0]["petition_dtls_seq"]);
                    info.transfer_type_seq = cn.ConvertNullInt(drc[0]["transfer_type_seq"]);
                    info.credit_card_no = drc[0]["credit_card_no"].ToString();
                    info.transfer_job_site_seq = cn.ConvertNullInt(drc[0]["transfer_job_site_seq"]);
                    info.transfer_job_site_name = drc[0]["transfer_job_site_name"].ToString();
                    info.remarks = drc[0]["remarks"].ToString();
                    info.bidder_col_Id = cn.ConvertNullInt(drc[0]["bidder_col_Id"]) ?? 0;

                    info.bgm_source_name = drc[0]["bgm_source_name"].ToString();
                    info.work_area_name = drc[0]["work_area_name"].ToString();
                    info.bgm_proj_oper_code = drc[0]["bgm_proj_oper_code"].ToString();
                    info.bgm_proj_oper_name = drc[0]["bgm_proj_oper_name"].ToString();
                    info.bgm_exp_grp_name = drc[0]["bgm_exp_grp_name"].ToString();
                    info.event_name = drc[0]["event_name"].ToString();
                    info.emp_name = drc[0]["emp_name"].ToString();
                    info.debtor_emp_name = drc[0]["debtor_emp_name"].ToString();
                    info.debtor_contact_name = drc[0]["debtor_contact_name"].ToString();
                    //info.debtor_contact_seq = cn.ConvertNullInt(drc[0]["debtor_contact_seq"]);
                    //info.debtor_contact_name = drc[0]["debtor_contact_name"].ToString();
                    info.contact_name = drc[0]["NameTh"].ToString();
                    info.borrow_type_name = drc[0]["borrow_type_name"].ToString();
                    info.percent_vat = cn.ConvertNullDouble(drc[0]["percent_vat"]);
                    info.percent_tax = cn.ConvertNullDouble(drc[0]["percent_tax"]);
                    info.total_amount = cn.ConvertNullDouble(drc[0]["total_amount"]);
                    info.total_amount_tax = cn.ConvertNullDouble(drc[0]["total_amount_tax"]);
                    info.total_amount_penalty = cn.ConvertNullDouble(drc[0]["total_amount_penalty"]);
                    info.total_amount_redemption = cn.ConvertNullDouble(drc[0]["total_amount_redemption"]);
                    info.total_amount_deduction = cn.ConvertNullDouble(drc[0]["total_amount_redemption"]);
                    info.total_amount_real = cn.ConvertNullDouble(drc[0]["total_amount_real"]);
                    info.petition_ref_no = drc[0]["petition_ref_no"].ToString();
                    info.petition_no = drc[0]["petition_no"].ToString();
                    info.petition_type_name = drc[0]["petition_type_name"].ToString();
                    info.payee_name = drc[0]["payee_name"].ToString();
                    info.return_amount = cn.ConvertNullDouble(drc[0]["return_amount"]);
                    info.receive_amount = cn.ConvertNullDouble(drc[0]["receive_amount"]);

                    info.approved_by = cn.ConvertNullInt(drc[0]["approved_by"]);
                    info.approved_when = cn.ConvertNullDateTime(drc[0]["approved_when"]);
                    info.tran_grp_seq = cn.ConvertNullInt(drc[0]["tran_grp_seq"]);

                    info.record_status = drc[0]["record_status"].ToString();
                    info.record_status_name = drc[0]["record_status_name"].ToString();
                    info.created_by = cn.ConvertNullInt(drc[0]["created_by"]);
                    info.created_when = cn.ConvertNullDateTime(drc[0]["created_when"]);
                    info.updated_by = cn.ConvertNullInt(drc[0]["updated_by"]);
                    info.updated_when = cn.ConvertNullDateTime(drc[0]["updated_when"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public M_FIM_MoneyRequests GetMoneyReqDNInfo(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_FIM_MoneyRequests info = new M_FIM_MoneyRequests();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT mr.money_req_seq, mr.bgm_source_seq, mr.bgm_year, mr.bgm_exp_grp_seq, mr.work_area_seq"
                    + ", mr.event_seq, mr.money_req_date, mr.use_advance, mr.ref_no, mr.doc_ref_no, mr.money_req_name"
                    + ", mr.bgm_proj_oper_seq, mr.emp_seq, mr.job_site_seq, mr.contact_seq, mr.icm_dn_seq"
                    + ", mr.advance_bgm_source_seq, mr.transfer_type_seq, mr.credit_card_no"

                    + ", s.bgm_source_name, area.work_area_name, pj.bgm_proj_oper_code, pj.bgm_proj_oper_name"
                    //+ ", concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name) as emp_name"
                    + ", coalesce(mrd.percent_vat, 0) as percent_vat"
                    + ", coalesce(mrd.percent_tax, 0) as percent_tax"
                    + ", c.NameTh as contact_name"
                    + ", coalesce(mrd.total_amount, 0) as total_amount"
                    + ", coalesce(mrd.total_amount_tax, 0) as total_amount_tax"
                    + ", coalesce(dni.total_amount_vat, 0) as total_amount_vat"
                    + ", coalesce(pen.total_amount_penalty, 0) as total_amount_penalty"
                    + ", coalesce(dn.RedemptionAmount, 0) as total_amount_redemption"
                    + ", coalesce(dn.DeductionAmount, 0) as total_amount_deduction"
                    //+ ", coalesce(mrd.total_amount, 0) - coalesce(mrd.total_amount_tax, 0) as total_amount_real"
                    + ", petd.petition_seq, petd.petition_dtls_seq"
                    + ", pet.ref_no AS petition_ref_no"
                    + ", CASE WHEN pet.petition_type = '0' THEN 'ขอเบิกจากรายการรอเบิก'"
                        + " ELSE 'ขอเบิกงบอุดหนุน'"
                        + " END AS petition_type_name"
                    + ", c.NameTh AS payee_name"
                    + ", dn.Code as icm_dn_ref_no"

                    + ", mr.approved_by, mr.approved_when, mr.tran_grp_seq"
                    + ", mr.remarks, mr.record_status, rs.record_status_name"
                    + ", mr.created_by, mr.created_when, mr.updated_by, mr.updated_when"
                    + " FROM fim_money_requests mr"
                    + " LEFT JOIN (select mrd.money_req_seq, mrd.percent_vat, mrd.percent_tax"
                        + ", sum(coalesce(mrd.amount, 0)) as total_amount"
                        + ", sum(coalesce(mrd.amount_tax, 0)) as total_amount_tax"
                        + " from fim_money_request_dtls mrd"
                        + " where mrd.record_status not like 'D'"
                        + " group by mrd.money_req_seq, mrd.percent_vat, mrd.percent_tax"
                        + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
                    + " LEFT JOIN IcmDeliveryNote dn ON mr.icm_dn_seq = dn.DnId"
                    + " LEFT JOIN (select dni.DnId, sum(coalesce(dni.VatAmount, 0)) as total_amount_vat"
                        + " from IcmDeliveryNoteItem dni"
                        + " group by dni.DnId"
                        + ") dni ON mr.icm_dn_seq = dni.DnId"
                    + " LEFT JOIN (select pen.DnId, sum(coalesce(pen.PenaltyFinalAmount, 0)) as total_amount_penalty"
                        + " from IcmDeliveryNotePenalty pen"
                        + " group by pen.DnPenId"
                        + ") pen ON mr.icm_dn_seq = pen.DnId"
                    + " LEFT JOIN bgm_project_operation pj ON mr.bgm_proj_oper_seq = pj.bgm_proj_oper_seq"
                    + " LEFT JOIN fim_mas_events event ON mr.event_seq = event.event_seq"
                    + " LEFT JOIN bgm_mas_source s ON mr.bgm_source_seq = s.bgm_source_seq"
                    + " LEFT JOIN hrm_mp_work_areas area ON mr.work_area_seq = area.work_area_seq"
                    + " LEFT JOIN IcmMasContact c ON mr.contact_seq = c.ConId"
                    + " LEFT JOIN fim_petition_dtls petd ON mr.money_req_seq = petd.money_req_seq"
                    + " LEFT JOIN fim_petitions pet ON petd.petition_seq = pet.petition_seq"
                    + " LEFT JOIN sys_record_status rs ON mr.record_status = rs.record_status"
                    + " WHERE mr.money_req_seq = @seq";
                

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.money_req_seq = Convert.ToInt32(drc[0]["money_req_seq"]);
                    info.bgm_source_seq = cn.ConvertNullInt(drc[0]["bgm_source_seq"]);
                    info.bgm_year = cn.ConvertNullInt(drc[0]["bgm_year"]);
                    info.bgm_exp_grp_seq = cn.ConvertNullInt(drc[0]["bgm_exp_grp_seq"]);
                    info.work_area_seq = cn.ConvertNullInt(drc[0]["work_area_seq"]);
                    info.event_seq = cn.ConvertNullInt(drc[0]["event_seq"]);
                    info.money_req_date = cn.ConvertNullDateTime(drc[0]["money_req_date"]);
                    info.use_advance = drc[0]["use_advance"].ToString();
                    info.advance_bgm_source_seq = cn.ConvertNullInt(drc[0]["advance_bgm_source_seq"]);
                    info.ref_no = drc[0]["ref_no"].ToString();
                    info.doc_ref_no = drc[0]["doc_ref_no"].ToString();
                    info.money_req_name = drc[0]["money_req_name"].ToString();
                    info.bgm_proj_oper_seq = cn.ConvertNullInt(drc[0]["bgm_proj_oper_seq"]);
                    info.emp_seq = cn.ConvertNullInt(drc[0]["emp_seq"]);
                    info.job_site_seq = cn.ConvertNullInt(drc[0]["job_site_seq"]);
                    info.contact_seq = cn.ConvertNullInt(drc[0]["contact_seq"]);
                    info.icm_dn_seq = cn.ConvertNullInt(drc[0]["DnId"]);
                    //info.approver_seq = cn.ConvertNullInt(drc[0]["approver_seq"]);
                    info.petition_seq = cn.ConvertNullInt(drc[0]["petition_seq"]);
                    info.petition_dtls_seq = cn.ConvertNullInt(drc[0]["petition_dtls_seq"]);
                    info.transfer_type_seq = cn.ConvertNullInt(drc[0]["transfer_type_seq"]);
                    info.credit_card_no = drc[0]["credit_card_no"].ToString();

                    info.bgm_source_name = drc[0]["bgm_source_name"].ToString();
                    info.work_area_name = drc[0]["work_area_name"].ToString();
                    info.bgm_proj_oper_code = drc[0]["bgm_proj_oper_code"].ToString();
                    info.bgm_proj_oper_name = drc[0]["bgm_proj_oper_name"].ToString();
                    info.contact_name = drc[0]["contact_name"].ToString();
                    info.percent_vat = cn.ConvertNullDouble(drc[0]["percent_vat"]);
                    info.percent_tax = cn.ConvertNullDouble(drc[0]["percent_tax"]);
                    info.total_amount = cn.ConvertNullDouble(drc[0]["total_amount"]);
                    info.total_amount_vat = cn.ConvertNullDouble(drc[0]["total_amount_vat"]);
                    info.total_amount_tax = cn.ConvertNullDouble(drc[0]["total_amount_tax"]);
                    info.total_amount_penalty = cn.ConvertNullDouble(drc[0]["total_amount_penalty"]);
                    info.total_amount_redemption = cn.ConvertNullDouble(drc[0]["total_amount_redemption"]);
                    info.total_amount_deduction = cn.ConvertNullDouble(drc[0]["total_amount_deduction"]);

                    info.petition_ref_no = drc[0]["petition_ref_no"].ToString();
                    info.petition_type_name = drc[0]["petition_type_name"].ToString();
                    info.payee_name = drc[0]["payee_name"].ToString();

                    info.dn_ref_no = drc[0]["icm_dn_ref_no"].ToString();

                    info.approved_by = cn.ConvertNullInt(drc[0]["approved_by"]);
                    info.approved_when = cn.ConvertNullDateTime(drc[0]["approved_when"]);
                    info.tran_grp_seq = cn.ConvertNullInt(drc[0]["tran_grp_seq"]);

                    info.record_status = drc[0]["record_status"].ToString();
                    info.record_status_name = drc[0]["record_status_name"].ToString();
                    info.created_by = cn.ConvertNullInt(drc[0]["created_by"]);
                    info.created_when = cn.ConvertNullDateTime(drc[0]["created_when"]);
                    info.updated_by = cn.ConvertNullInt(drc[0]["updated_by"]);
                    info.updated_when = cn.ConvertNullDateTime(drc[0]["updated_when"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public bool HaveAmountExist(int money_req_seq)
        {
            string strSQL;
            DataTable dtb;
            bool bPass = true;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@money_req_seq", money_req_seq);
                dbManager.AddParameters(1, "@delete_record_status", SystemInfo._DeleteRecordStatus);

                //strSQL = "select coalesce(rqd.amount, 0) as amount, coalesce(opre.remain_amount, 0) as remain_amount"
                //    + " from fim_money_requests rq"
                //    + " left join (select rqd.money_req_seq"
                //        + ", sum(coalesce(rqd.amount, 0)) as amount"
                //        + " from fim_money_request_dtls rqd"
                //        + " where rqd.record_status not like @delete_record_status"
                //        + " group by rqd.money_req_seq"
                //        + ") rqd on rq.money_req_seq = rqd.money_req_seq"
                //    + " left join (select ope.bgm_proj_oper_seq"
                //        + ", sum(coalesce(opre.cal_amount, 0)) as remain_amount"
                //        + " from bgm_project_operation_report opre"
                //        + " left join bgm_project_operation_expense ope on opre.bgm_proj_oper_exp_seq = ope.bgm_proj_oper_exp_seq"
                //        + " where opre.record_status not like @delete_record_status"
                //        + " and ope.record_status not like @delete_record_status"
                //        + " group by ope.bgm_proj_oper_seq"
                //        + ") opre on rq.bgm_proj_oper_seq = opre.bgm_proj_oper_seq"

                //    + " WHERE rq.money_req_seq = @money_req_seq";

                strSQL = @"
select coalesce(rqd.amount, 0) as amount, coalesce(allrpt.remain_amount, 0) - coalesce(rpt.cal_amount, 0) as remain_amount

from fim_money_requests rq

left join (select rqd.money_req_seq
, sum(coalesce(rqd.amount, 0)) as amount
from fim_money_request_dtls rqd
where rqd.record_status not like @delete_record_status
group by rqd.money_req_seq
) rqd on rq.money_req_seq = rqd.money_req_seq

left join (select dni.DnId, sum(coalesce(rpt.cal_amount, 0)) as cal_amount
from IcmDeliveryNoteItem dni
left join IcmPurchaseOrderInstallmentItem poii on dni.PoInstallItemId = poii.PoInstallItemId
left join IcmBidderQuotation quo on poii.BidderQuoId = quo.BidderQuoId
left join IcmPurchaseRequestItem pri on quo.PrItemId = pri.PrItemId
left join bgm_project_operation_report rpt on pri.PrItemId = rpt.icm_pr_item_seq
where rpt.record_status <> 'D'
group by dni.DnId
) rpt on rq.icm_dn_seq = rpt.DnId

left join (select ope.bgm_proj_oper_seq, pje.bgm_source_seq, sum(coalesce(rpt.cal_amount, 0)) as remain_amount
from bgm_project_operation_expense ope
left join bgm_project_expense pje on ope.bgm_proj_exp_seq = pje.bgm_proj_exp_seq
left join bgm_project_operation_report rpt on ope.bgm_proj_oper_exp_seq = rpt.bgm_proj_oper_exp_seq
where ope.record_status not like 'D'
and rpt.record_status not like 'D'
group by ope.bgm_proj_oper_seq, pje.bgm_source_seq
) allrpt on rq.bgm_proj_oper_seq = allrpt.bgm_proj_oper_seq
	and rq.bgm_source_seq = allrpt.bgm_source_seq

where rq.money_req_seq = @money_req_seq";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                for (int i = 0; i < dtb.Rows.Count; i++)
                {
                    double dBathAmount = cn.ConvertNullDouble(dtb.Rows[i]["amount"]) ?? 0;
                    double dRemainAmount = cn.ConvertNullDouble(dtb.Rows[i]["remain_amount"]) ?? 0;

                    if (dBathAmount > dRemainAmount)
                    {
                        bPass = false;
                        return bPass;
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return bPass;
        }


        public IList<M_FIM_MoneyRequests> GetReturnDate(Nullable<int> bgm_source_seq
           , Nullable<int> bgm_year, Nullable<int> work_area_seq, Nullable<int> event_seq
           , Nullable<int> contact_seq, Nullable<int> emp_seq, string record_status, DateTime? dFromDate, DateTime? dToDate, bool bReadyToPay)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_MoneyRequests> infoList = new List<M_FIM_MoneyRequests>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(10);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@bgm_year", bgm_year);
                dbManager.AddParameters(3, "@work_area_seq", work_area_seq);
                dbManager.AddParameters(4, "@event_seq", event_seq);
                dbManager.AddParameters(5, "@contact_seq", contact_seq);
                dbManager.AddParameters(6, "@emp_seq", emp_seq);
                dbManager.AddParameters(7, "@record_status", record_status);
                dbManager.AddParameters(8, "@from_date", dFromDate);
                dbManager.AddParameters(9, "@to_date", dToDate);

                strSQL = "SELECT mr.money_req_seq, mr.bgm_source_seq, mr.bgm_year, mr.bgm_exp_grp_seq, mr.work_area_seq"
                    + ", mr.event_seq, mr.money_req_date, mr.use_advance, mr.ref_no, mr.doc_ref_no, mr.money_req_name"
                    + ", mr.money_req_name_plus"
                    + ", mr.bgm_proj_oper_seq, mr.emp_seq, mr.job_site_seq, mr.contact_seq, mr.icm_dn_seq, mr.approver_seq"
                    + ", mr.travel_req_seq, mr.borrow_no, mr.borrow_type_seq"
                    + ", mr.borrow_start_date, mr.borrow_end_date, mr.borrow_return_date, mr.allow"
                    + ", mr.debtor_emp_seq, mr.debtor_contact_seq"
                    + ", mr.advance_bgm_source_seq, mr.transfer_type_seq"
                    + ", mr.credit_card_no"

                    + ", s.bgm_source_name, area.work_area_name, pj.bgm_proj_oper_code, pj.bgm_proj_oper_name, event.event_name"
                    + ", concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name) as emp_name"
                    //+ ", concat(approvenp.prefix_name, approve.first_name, ' ', approve.last_name) as approve_name"
                    + ", concat(debtornp.prefix_name, debtor.first_name, ' ', debtor.last_name) as debtor_emp_name"
                    //+ ", debtorcp.contact_person_name as debtor_contact_name"
                    //+ ", debtorcp.contact_seq as debtor_contact_seq"
                    + ", debtorc.NameTh as debtor_contact_name"
                    + ", c.NameTh"
                    + ", bt.borrow_type_name, mrd.total_amount"
                    + ", j.job_site_name, s.use_petition, pay.pay_seq"

                    + ", mr.approved_by, mr.approved_when, mr.tran_grp_seq"
                    + ", mr.remarks, mr.record_status, rs.record_status_name"
                    + ", mr.created_by, mr.created_when, mr.updated_by, mr.updated_when"
                    + " FROM fim_money_requests mr"
                    + " LEFT JOIN (select mrd.money_req_seq"
                        + ", sum(coalesce(mrd.amount, 0)) as total_amount"
                        + " from fim_money_request_dtls mrd"
                        + " where mrd.record_status not like @delete_status"
                        + " group by mrd.money_req_seq"
                        + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
                    + " LEFT JOIN bgm_project_operation pj ON mr.bgm_proj_oper_seq = pj.bgm_proj_oper_seq"
                    + " LEFT JOIN fim_mas_events event ON mr.event_seq = event.event_seq"
                    + " LEFT JOIN bgm_mas_source s ON mr.bgm_source_seq = s.bgm_source_seq"
                    + " LEFT JOIN hrm_mp_work_areas area ON mr.work_area_seq = area.work_area_seq"
                    + " LEFT JOIN IcmMasContact c ON mr.contact_seq = c.ConId"
                    + " LEFT JOIN hrm_ps_employees emp ON mr.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes empnp ON emp.name_prefix_seq = empnp.name_prefix_seq"
                    + " LEFT JOIN hrm_mp_job_sites j ON mr.job_site_seq = j.job_site_seq"
                    + " LEFT JOIN fim_payments pay on mr.money_req_seq = pay.money_req_seq"
                    //+ " LEFT JOIN hrm_ps_employees approve ON mr.approver_seq = approve.emp_seq"
                    //+ " LEFT JOIN mas_name_prefixes approvenp ON approve.name_prefix_seq = approvenp.name_prefix_seq"
                    + " LEFT JOIN hrm_ps_employees debtor ON mr.debtor_emp_seq = debtor.emp_seq"
                    + " LEFT JOIN mas_name_prefixes debtornp ON debtor.name_prefix_seq = debtornp.name_prefix_seq"
                    //+ " LEFT JOIN fim_mas_contact_persons debtorcp ON mr.debtor_contact_seq = debtorcp.contact_person_seq"
                    + " LEFT JOIN IcmMasContact debtorc ON mr.debtor_contact_seq = debtorc.ConId"
                    + " LEFT JOIN fim_mas_borrow_types bt ON mr.borrow_type_seq = bt.borrow_type_seq"
                    + " LEFT JOIN sys_record_status rs ON mr.record_status = rs.record_status"
                    + (record_status != null ?
                        (record_status == "W" ? " WHERE mr.record_status in ('P', 'W') and (mr.allow = '1' or mr.allow is null)"
                            : " WHERE mr.record_status in ('A') and (mr.allow = '1' or mr.allow is null)")
                        : " WHERE mr.record_status not like @delete_status")
                    + (bgm_source_seq != null ? " AND mr.bgm_source_seq = @bgm_source_seq" : "")
                    + (bgm_year != null ? " AND mr.bgm_year = @bgm_year" : "")
                    + (work_area_seq != null ? " AND mr.work_area_seq = @work_area_seq" : "")
                    + (event_seq != null ? " AND mr.event_seq = @event_seq" : "")
                    + (contact_seq != null ? " AND mr.contact_seq = @contact_seq" : "")
                    + (emp_seq != null ? " AND mr.emp_seq = @emp_seq" : "")
                    + (dFromDate != null ? " and mr.money_req_date >= @from_date" : "")
                    + (dToDate != null ? " and mr.money_req_date <= @to_date" : "")
                    + " ORDER BY mr.money_req_date";
                

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_MoneyRequests info = new M_FIM_MoneyRequests();
                        info.money_req_seq = Convert.ToInt32(drc[i]["money_req_seq"]);
                        info.bgm_source_seq = cn.ConvertNullInt(drc[i]["bgm_source_seq"]);
                        info.bgm_year = cn.ConvertNullInt(drc[i]["bgm_year"]);
                        info.bgm_exp_grp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_grp_seq"]);
                        info.work_area_seq = cn.ConvertNullInt(drc[i]["work_area_seq"]);
                        info.event_seq = cn.ConvertNullInt(drc[i]["event_seq"]);
                        info.money_req_date = cn.ConvertNullDateTime(drc[i]["money_req_date"]);
                        info.use_advance = drc[i]["use_advance"].ToString();
                        info.advance_bgm_source_seq = cn.ConvertNullInt(drc[i]["advance_bgm_source_seq"]);
                        info.ref_no = drc[i]["ref_no"].ToString();
                        info.doc_ref_no = drc[i]["doc_ref_no"].ToString();
                        info.money_req_name = drc[i]["money_req_name"].ToString();
                        info.money_req_name_plus = drc[i]["money_req_name_plus"].ToString();
                        info.bgm_proj_oper_seq = cn.ConvertNullInt(drc[i]["bgm_proj_oper_seq"]);
                        info.emp_seq = cn.ConvertNullInt(drc[i]["emp_seq"]);
                        info.job_site_seq = cn.ConvertNullInt(drc[i]["job_site_seq"]);
                        info.contact_seq = cn.ConvertNullInt(drc[i]["contact_seq"]);
                        info.icm_dn_seq = cn.ConvertNullInt(drc[i]["icm_dn_seq"]);
                        info.approver_seq = cn.ConvertNullInt(drc[i]["approver_seq"]);
                        info.travel_req_seq = cn.ConvertNullInt(drc[i]["travel_req_seq"]);
                        info.borrow_no = drc[i]["borrow_no"].ToString();
                        info.borrow_type_seq = cn.ConvertNullInt(drc[i]["borrow_type_seq"]);
                        info.borrow_start_date = cn.ConvertNullDateTime(drc[i]["borrow_start_date"]);
                        info.borrow_end_date = cn.ConvertNullDateTime(drc[i]["borrow_end_date"]);
                        info.borrow_return_date = cn.ConvertNullDateTime(drc[i]["borrow_return_date"]);
                        info.debtor_emp_seq = cn.ConvertNullInt(drc[i]["debtor_emp_seq"]);
                        info.debtor_contact_seq = cn.ConvertNullInt(drc[i]["debtor_contact_seq"]);
                        info.transfer_type_seq = cn.ConvertNullInt(drc[i]["transfer_type_seq"]);
                        info.credit_card_no = drc[i]["credit_card_no"].ToString();

                        info.bgm_source_name = drc[i]["bgm_source_name"].ToString();
                        info.work_area_name = drc[i]["work_area_name"].ToString();
                        info.bgm_proj_oper_code = drc[i]["bgm_proj_oper_code"].ToString();
                        info.bgm_proj_oper_name = drc[i]["bgm_proj_oper_name"].ToString();
                        info.event_name = drc[i]["event_name"].ToString();
                        info.emp_name = drc[i]["emp_name"].ToString();
                        info.debtor_emp_name = drc[i]["debtor_emp_name"].ToString();
                        info.debtor_contact_name = drc[i]["debtor_contact_name"].ToString();
                        //info.debtor_contact_seq = cn.ConvertNullInt(drc[i]["debtor_contact_seq"]);
                        //info.debtor_contact_name = drc[i]["debtor_contact_name"].ToString();
                        info.contact_name = drc[i]["NameTh"].ToString();
                        info.borrow_type_name = drc[i]["borrow_type_name"].ToString();
                        info.total_amount = cn.ConvertNullDouble(drc[i]["total_amount"]);
                        info.job_site_name = drc[i]["job_site_name"].ToString();

                        info.approved_by = cn.ConvertNullInt(drc[i]["approved_by"]);
                        info.approved_when = cn.ConvertNullDateTime(drc[i]["approved_when"]);
                        info.tran_grp_seq = cn.ConvertNullInt(drc[i]["tran_grp_seq"]);

                        info.record_status = drc[i]["record_status"].ToString();
                        info.record_status_name = drc[i]["record_status_name"].ToString();
                        info.created_by = cn.ConvertNullInt(drc[i]["created_by"]);
                        info.created_when = cn.ConvertNullDateTime(drc[i]["created_when"]);
                        info.updated_by = cn.ConvertNullInt(drc[i]["updated_by"]);
                        info.updated_when = cn.ConvertNullDateTime(drc[i]["updated_when"]);
                        //infoList.Add(info);

                        if (bReadyToPay)
                        {
                            if (info.record_status == "A" && cn.ConvertNullInt(drc[i]["pay_seq"]) == null)
                            {
                                if (drc[i]["use_petition"].ToString() == "1")
                                {
                                    if (info.use_advance == "0")
                                    {
                                        if (ReadyToPay(info.money_req_seq))
                                        {
                                            infoList.Add(info);
                                        }
                                    }
                                }
                                else
                                {
                                    infoList.Add(info);
                                }
                            }
                        }
                        else
                        {
                            infoList.Add(info);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        //public string SelectRecentInsertedRoleSeq()
        //{
        //    string strSQL;
        //    string strResult;

        //    try
        //    {
        //        dbManager.Open();

        //        strSQL = "select scope_identity() as last_seq from fim_money_requests";

        //        strResult = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0]["last_seq"].ToString();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    return strResult;
        //}

        public string Insert(M_FIM_MoneyRequests info)
        {
            string strSeq = "";
            string strSQL;
            try
            {
                string strRefNo = info.ref_no; //GetNextRefNo(info.bgm_year, info.bgm_source_seq);
                dbManager.Open();
                dbManager.CreateParameters(36);
                dbManager.BeginTransaction();

                dbManager.AddParameters(0, "@money_req_seq", info.money_req_seq);
                dbManager.AddParameters(1, "@bgm_source_seq", info.bgm_source_seq);
                dbManager.AddParameters(2, "@bgm_year", info.bgm_year);
                dbManager.AddParameters(3, "@bgm_exp_grp_seq", info.bgm_exp_grp_seq);
                dbManager.AddParameters(4, "@work_area_seq", info.work_area_seq);
                dbManager.AddParameters(5, "@event_seq", info.event_seq);
                dbManager.AddParameters(6, "@money_req_date", info.money_req_date);
                dbManager.AddParameters(7, "@use_advance", info.use_advance);
                dbManager.AddParameters(8, "@advance_bgm_source_seq", info.advance_bgm_source_seq);
                dbManager.AddParameters(9, "@ref_no", strRefNo);
                dbManager.AddParameters(10, "@doc_ref_no", info.doc_ref_no);
                dbManager.AddParameters(11, "@money_req_name", info.money_req_name);
                dbManager.AddParameters(12, "@bgm_proj_oper_seq", info.bgm_proj_oper_seq);
                dbManager.AddParameters(13, "@emp_seq", info.emp_seq);
                dbManager.AddParameters(14, "@job_site_seq", info.job_site_seq);
                dbManager.AddParameters(15, "@contact_seq", info.contact_seq);
                dbManager.AddParameters(16, "@icm_dn_seq", info.icm_dn_seq);
                dbManager.AddParameters(17, "@approver_seq", info.approver_seq);
                dbManager.AddParameters(18, "@travel_req_seq", info.travel_req_seq);
                dbManager.AddParameters(19, "@borrow_no", info.borrow_no);
                dbManager.AddParameters(20, "@borrow_type_seq", info.borrow_type_seq);
                dbManager.AddParameters(21, "@borrow_start_date", info.borrow_start_date);
                dbManager.AddParameters(22, "@borrow_end_date", info.borrow_end_date);
                dbManager.AddParameters(23, "@borrow_return_date", info.borrow_return_date);
                dbManager.AddParameters(24, "@debtor_emp_seq", info.debtor_emp_seq);
                dbManager.AddParameters(25, "@debtor_contact_seq", info.debtor_contact_seq);
                dbManager.AddParameters(26, "@remarks", info.remarks);
                dbManager.AddParameters(27, "@money_req_name_plus", info.money_req_name_plus);

                dbManager.AddParameters(28, "@record_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(29, "@created_by", info.created_by);
                dbManager.AddParameters(30, "@created_when", info.created_when);
                dbManager.AddParameters(31, "@transfer_type_seq", info.transfer_type_seq);
                dbManager.AddParameters(32, "@credit_card_no", info.credit_card_no);
                dbManager.AddParameters(33, "@allow", info.allow);
                dbManager.AddParameters(34, "@transfer_job_site_seq", info.transfer_job_site_seq);
                dbManager.AddParameters(35, "@bidder_col_Id", info.bidder_col_Id);

                strSQL = "INSERT INTO fim_money_requests"
                    + "(bgm_source_seq, bgm_year, bgm_exp_grp_seq, work_area_seq, event_seq, money_req_date"
                    + ", use_advance, advance_bgm_source_seq, ref_no, doc_ref_no, money_req_name, money_req_name_plus"
                    + ", bgm_proj_oper_seq, emp_seq, job_site_seq, contact_seq, icm_dn_seq, approver_seq, travel_req_seq, borrow_no"
                    + ", borrow_type_seq, borrow_start_date, borrow_end_date, borrow_return_date"
                    + ", debtor_emp_seq, debtor_contact_seq, allow"
                    + ", record_status, created_by, created_when, transfer_type_seq, credit_card_no, transfer_job_site_seq, bidder_col_Id)"
                    + " VALUES(@bgm_source_seq"
                        + ", @bgm_year"
                        + ", @bgm_exp_grp_seq"
                        + ", @work_area_seq"
                        + ", @event_seq"
                        + ", @money_req_date"
                        + ", @use_advance"
                        + ", @advance_bgm_source_seq"
                        + ", @ref_no"
                        + ", @doc_ref_no"
                        + ", @money_req_name"
                        + ", @money_req_name_plus"
                        + ", @bgm_proj_oper_seq"
                        + ", @emp_seq"
                        + ", @job_site_seq"
                        + ", @contact_seq"
                        + ", @icm_dn_seq"
                        + ", @approver_seq"
                        + ", @travel_req_seq"
                        + ", @borrow_no"
                        + ", @borrow_type_seq"
                        + ", @borrow_start_date"
                        + ", @borrow_end_date"
                        + ", @borrow_return_date"
                        + ", @debtor_emp_seq"
                        + ", @debtor_contact_seq"
                        + ", @allow"

                        + ", @record_status"
                        + ", @created_by"
                        + ", @created_when"
                        + ", @transfer_type_seq"
                        + ", @credit_card_no"
                        + ", @transfer_job_site_seq"
                        + ", @bidder_col_Id);"
                    + " select scope_identity();";
                DataTable dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                strSeq = Convert.ToString(dt.Rows[0][0]);

                dbManager.CommitTransaction();
                return strSeq;
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(M_FIM_MoneyRequests info)
        {
            string strSQL;
            try
            {
                if (string.IsNullOrEmpty(info.ref_no))
                {
                    info.ref_no = GetInfo(info.money_req_seq).ref_no;
                }

                dbManager.Open();
                dbManager.CreateParameters(34);

                dbManager.AddParameters(0, "@money_req_seq", info.money_req_seq);
                dbManager.AddParameters(1, "@bgm_source_seq", info.bgm_source_seq);
                dbManager.AddParameters(2, "@bgm_year", info.bgm_year);
                dbManager.AddParameters(3, "@bgm_exp_grp_seq", info.bgm_exp_grp_seq);
                dbManager.AddParameters(4, "@work_area_seq", info.work_area_seq);
                dbManager.AddParameters(5, "@event_seq", info.event_seq);
                dbManager.AddParameters(6, "@money_req_date", info.money_req_date);
                dbManager.AddParameters(7, "@use_advance", info.use_advance);
                dbManager.AddParameters(8, "@advance_bgm_source_seq", info.advance_bgm_source_seq);
                dbManager.AddParameters(9, "@ref_no", info.ref_no);
                dbManager.AddParameters(10, "@doc_ref_no", info.doc_ref_no);
                dbManager.AddParameters(11, "@money_req_name", info.money_req_name);
                dbManager.AddParameters(12, "@bgm_proj_oper_seq", info.bgm_proj_oper_seq);
                dbManager.AddParameters(13, "@emp_seq", info.emp_seq);
                dbManager.AddParameters(14, "@job_site_seq", info.job_site_seq);
                dbManager.AddParameters(15, "@contact_seq", info.contact_seq);
                dbManager.AddParameters(16, "@icm_dn_seq", info.icm_dn_seq);
                dbManager.AddParameters(17, "@approver_seq", info.approver_seq);
                dbManager.AddParameters(18, "@travel_req_seq", info.travel_req_seq);
                dbManager.AddParameters(19, "@borrow_no", info.borrow_no);
                dbManager.AddParameters(20, "@borrow_type_seq", info.borrow_type_seq);
                dbManager.AddParameters(21, "@borrow_start_date", info.borrow_start_date);
                dbManager.AddParameters(22, "@borrow_end_date", info.borrow_end_date);
                dbManager.AddParameters(23, "@borrow_return_date", info.borrow_return_date);
                dbManager.AddParameters(24, "@debtor_emp_seq", info.debtor_emp_seq);
                dbManager.AddParameters(25, "@debtor_contact_seq", info.debtor_contact_seq);
                dbManager.AddParameters(26, "@remarks", info.remarks);
                dbManager.AddParameters(27, "@money_req_name_plus", info.money_req_name_plus);

                dbManager.AddParameters(28, "@updated_by", info.updated_by);
                dbManager.AddParameters(29, "@updated_when", info.updated_when);
                dbManager.AddParameters(30, "@transfer_type_seq", info.transfer_type_seq);
                dbManager.AddParameters(31, "@credit_card_no", info.credit_card_no);
                dbManager.AddParameters(32, "@transfer_job_site_seq", info.transfer_job_site_seq);
                dbManager.AddParameters(33, "@bidder_col_Id", info.bidder_col_Id);

                strSQL = "UPDATE fim_money_requests"
                    + " set bgm_source_seq = @bgm_source_seq"
                    + ", bgm_year = @bgm_year"
                    + ", bgm_exp_grp_seq = @bgm_exp_grp_seq"
                    + ", work_area_seq = @work_area_seq"
                    //+ ", event_seq = @event_seq"
                    + ", money_req_date = @money_req_date"
                    //+ ", use_advance = @use_advance"
                    //+ ", advance_bgm_source_seq = @advance_bgm_source_seq"
                    + ", ref_no = @ref_no"
                    + ", doc_ref_no = @doc_ref_no"
                    + ", money_req_name = @money_req_name"
                    + ", money_req_name_plus = @money_req_name_plus"
                    + ", bgm_proj_oper_seq = @bgm_proj_oper_seq"
                    + ", emp_seq = @emp_seq"
                    + ", job_site_seq = @job_site_seq"
                    + ", contact_seq = @contact_seq"
                    + ", icm_dn_seq = @icm_dn_seq"
                    + ", approver_seq = @approver_seq"
                    + ", travel_req_seq = @travel_req_seq"
                    + ", borrow_no = @borrow_no"
                    + ", borrow_type_seq = @borrow_type_seq"
                    + ", borrow_start_date = @borrow_start_date"
                    + ", borrow_end_date = @borrow_end_date"
                    + ", borrow_return_date = @borrow_return_date"
                    + ", debtor_emp_seq = @debtor_emp_seq"
                    + ", debtor_contact_seq = @debtor_contact_seq"
                    + ", remarks = @remarks"
                    + ", transfer_type_seq = @transfer_type_seq"
                    + ", credit_card_no = @credit_card_no"
                    + ", transfer_job_site_seq = @transfer_job_site_seq"
                    + ", bidder_col_Id = @bidder_col_Id"

                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE money_req_seq = @money_req_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateBidder(M_FIM_MoneyRequests info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@event_seq", info.event_seq);
                dbManager.AddParameters(1, "@bidder_col_Id", info.bidder_col_Id);
              

                strSQL = "UPDATE fim_money_requests"
                    + " SET event_seq = @event_seq"
                    + " WHERE bidder_col_Id = @bidder_col_Id";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateBidderCol(int? BidderColId)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@BidderColId", BidderColId);


                strSQL = "UPDATE IcmBidderCollateral"
                    + " SET ColStatusId = 11"
                    + " WHERE BidderColId = @BidderColId";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(M_FIM_MoneyRequests info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@money_req_seq", info.money_req_seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_money_requests"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE money_req_seq = @money_req_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                strSQL = "UPDATE fim_money_request_dtls"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE money_req_seq = @money_req_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateBgmExpGrpSeq(M_FIM_MoneyRequests info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@money_req_seq", info.money_req_seq);
                dbManager.AddParameters(1, "@bgm_exp_grp_seq", info.bgm_exp_grp_seq);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_money_requests"
                    + " SET bgm_exp_grp_seq = @bgm_exp_grp_seq"
                    + " WHERE money_req_seq = @money_req_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateUseAdvance(string money_req_seq, string use_advance, string remarks, string updated_by)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@money_req_seq", money_req_seq);
                dbManager.AddParameters(1, "@use_advance", use_advance);
                dbManager.AddParameters(2, "@remarks", remarks);
                dbManager.AddParameters(3, "@updated_by", updated_by);
                dbManager.AddParameters(4, "@updated_when", DateTime.Now);

                strSQL = "UPDATE fim_money_requests"
                    + " SET use_advance = @use_advance"
                    + ", remarks = @remarks"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE money_req_seq = @money_req_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateGlTranSeq(M_FIM_MoneyRequests info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@money_req_seq", info.money_req_seq);
                dbManager.AddParameters(1, "@tran_grp_seq", info.tran_grp_seq);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_money_requests"
                    + " SET tran_grp_seq = @tran_grp_seq"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE money_req_seq = @money_req_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateBorrowAllow(M_FIM_MoneyRequests info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);

                dbManager.AddParameters(0, "@money_req_seq", info.money_req_seq);
                dbManager.AddParameters(1, "@allow", info.allow);
                dbManager.AddParameters(2, "@record_status", info.record_status);
                dbManager.AddParameters(3, "@updated_by", info.updated_by);
                dbManager.AddParameters(4, "@updated_when", info.updated_when);
                dbManager.AddParameters(5, "@borrow_no", info.borrow_no);

                strSQL = "UPDATE fim_money_requests"
                    + " SET allow = @allow"
                    + ", borrow_no = @borrow_no"
                    + ", record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE money_req_seq = @money_req_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void ChangeStatus(M_FIM_MoneyRequests info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@money_req_seq", info.money_req_seq);
                dbManager.AddParameters(1, "@record_status", info.record_status);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_money_requests"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE money_req_seq = @money_req_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        //Modified by Raksakaew on March 3,2014
        public DataTable Report_Contract(int seq)
        {
            string strSQL;
            DataTable dtb = null;
            bool bEmployee = true;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT debtor_contact_seq"
                    + " FROM fim_money_requests"
                    + " WHERE money_req_seq = @seq";
                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                if (dtb.Rows.Count > 0)
                {
                    if (cn.ConvertNullInt(dtb.Rows[0]["debtor_contact_seq"]) != null)
                    {
                        bEmployee = false;
                    }
                }

                strSQL = "SELECT mr.money_req_seq, mr.bgm_source_seq, mr.bgm_year"
                    + ", DATEADD(year, 543, mr.money_req_date) as money_req_date"
                    + ", mr.use_advance, mr.ref_no, mr.doc_ref_no"
                    + ", mr.money_req_name, mr.money_req_name_plus"
                    + ", mr.bgm_proj_oper_seq, mr.emp_seq"
                    + ", mr.job_site_seq, mr.approver_seq"
                    + ", mr.borrow_no, mr.borrow_type_seq"
                    + ", mr.borrow_start_date"
                    + ", mr.borrow_end_date"
                    + ", mr.borrow_return_date"
                    + ", mrd.amount, s.bgm_source_name, bt.borrow_type_name"
                    + ", bt.return_day, bt.cal_date_type"
                    + ", case when bt.cal_date_type = '1' then 'นับจากวันที่ได้รับเงิน'"
                        + " else 'นับจากวันที่เดินทางกลับ'"
                        + " end as cal_date_type_name"
                    + (bEmployee ? ", mr.debtor_emp_seq as debtor_seq" 
                        + ", concat(coalesce(np.prefix_short, ''), coalesce(emp.first_name, ''), ' ', coalesce(emp.last_name, '')) as debtor_name"
                        + ", pos.pos_name as debtor_pos_name"
                        + ", j.job_site_name as debtor_job_site_name"
                        + ", prov.prov_name as debtor_prov_name" 
                                       
                        : ", mr.debtor_contact_seq as debtor_seq"
                        + ", cp.NameTh as debtor_name"
                        + ", '' as debtor_pos_name"
                        + ", '' as debtor_job_site_name"
                        + ", '' as debtor_prov_name")

                    // allower --
                    + ", concat(coalesce(alnp.prefix_short, ''), coalesce(al.first_name, ''), ' ', coalesce(al.last_name, '')) as allower_name"

                    // approver --
                    + ", concat(coalesce(apnp.prefix_short, ''), coalesce(ap.first_name, ''), ' ', coalesce(ap.last_name, '')) as approver_name"
                    + ", mr.credit_card_no"

                    + " FROM fim_money_requests mr"
                    + " LEFT JOIN (SELECT money_req_seq"
                        + ", sum(coalesce(amount, 0)) as amount"
                        + " FROM fim_money_request_dtls"
                        + " WHERE record_status not like @delete_status"
                        + " GROUP BY money_req_seq"
                        + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
                    + " LEFT JOIN bgm_mas_source s ON mr.bgm_source_seq = s.bgm_source_seq"
                    + " LEFT JOIN fim_mas_borrow_types bt ON mr.borrow_type_seq = bt.borrow_type_seq"

                    + (bEmployee ? " LEFT JOIN hrm_ps_employees emp ON mr.debtor_emp_seq = emp.emp_seq"
                        + " LEFT JOIN mas_name_prefixes np ON emp.name_prefix_seq = np.name_prefix_seq"
                        + " LEFT JOIN hrm_mp_positions pos ON emp.pos_seq = pos.pos_seq"
                        + " LEFT JOIN hrm_mp_job_sites j ON emp.working_job_site_seq = j.job_site_seq"
                        + " LEFT JOIN mas_provinces prov ON j.provnce_seq = prov.prov_seq"

                        //: " LEFT JOIN fim_mas_contact_persons cp ON mr.debtor_contact_seq = cp.contact_person_seq"
                        : " LEFT JOIN IcmMasContact c ON mr.debtor_contact_seq = c.ConId")

                    // allower --
                    + " LEFT JOIN hrm_ps_employees al ON mr.approver_seq = al.emp_seq"
                    + " LEFT JOIN mas_name_prefixes alnp ON al.name_prefix_seq = alnp.name_prefix_seq"

                    // approver --
                    + " LEFT JOIN hrm_ps_employees ap ON mr.approved_by = ap.emp_seq"
                    + " LEFT JOIN mas_name_prefixes apnp ON ap.name_prefix_seq = apnp.name_prefix_seq"
                    + " WHERE mr.money_req_seq = @seq";
                

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        //public DataTable Report_Contract(int seq)
        //{
        //    string strSQL;
        //    DataTable dtb = null;
         
        //    try
        //    {
        //        dbManager.Open();
        //        dbManager.CreateParameters(2);

        //        dbManager.AddParameters(0, "@seq", seq);
        //        dbManager.AddParameters(1, "@delete_status", SystemInfo._DeleteRecordStatus);

        //        strSQL = "SELECT * FROM vwReportContract WHERE money_req_seq = @seq";
                
        //        dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
        //    }
        //    catch (Exception ex)
        //    {
        //        throw (ex);
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    return dtb;
        //}

        //Modified by Raksakaew on March 3,2014
        public DataTable Report_ContractSub(int seq)
        {
            string strSQL;
            DataTable dtb = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@delete_status", SystemInfo._DeleteRecordStatus);

                //Modified by Raksakaew Feb 26,2014 add mrd.remarks
                strSQL = "SELECT exp.bgm_exp_name, mrd.remarks as remarks, mrd.amount as amount"
                    + " FROM fim_money_request_dtls mrd"
                    + " LEFT JOIN bgm_project_operation_expense opexp ON mrd.bgm_proj_oper_exp_seq = opexp.bgm_proj_oper_exp_seq"
                    + " LEFT JOIN bgm_project_expense pjexp ON opexp.bgm_proj_exp_seq = pjexp.bgm_proj_exp_seq"
                    + " LEFT JOIN bgm_mas_expense exp ON pjexp.bgm_exp_seq = exp.bgm_exp_seq"
                    + " WHERE mrd.money_req_seq = @seq"
                    + " AND mrd.record_status not like @delete_status";
                

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        //public DataTable Report_ContractSub(int seq)
        //{
        //    string strSQL;
        //    DataTable dtb = null;
        //    try
        //    {
        //        dbManager.Open();
        //        dbManager.CreateParameters(2);

        //        dbManager.AddParameters(0, "@seq", seq);
        //        dbManager.AddParameters(1, "@delete_status", SystemInfo._DeleteRecordStatus);

        //        //Modified by Raksakaew Feb 26,2014 add mrd.remarks
        //        strSQL = "SELECT * FROM vwReportContractSub WHERE money_req_seq = @seq AND record_status not like @delete_status";                

        //        dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
        //    }
        //    catch (Exception ex)
        //    {
        //        throw (ex);
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    return dtb;
        //}

        public string GetNextRefNo(Nullable<int> bgm_year, Nullable<int> bgm_source_seq)//, string use_advance)
        {
            string strSQL;
            DataTable dtb = null;
            string strRefNo = "";
            string strResult = bgm_year.ToString() + "/0001"; ;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@bgm_year", bgm_year);

                strSQL = "SELECT mr.bgm_source_seq, mr.bgm_year, max(mr.ref_no) as ref_no"
                    + " FROM fim_money_requests mr"
                    + " WHERE mr.record_status not like @delete_status"
                    + (bgm_source_seq != null ? " and mr.bgm_source_seq = @bgm_source_seq" : "")
                    + (bgm_year != null ? "" : " and mr.bgm_year = @bgm_year")
                    + " group by mr.bgm_source_seq, mr.bgm_year";
                

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                if (dtb.Rows.Count > 0)
                {
                    strRefNo = dtb.Rows[0]["ref_no"].ToString();

                    if (strRefNo.Length > 0)
                    {
                        strRefNo = strRefNo.Split('/')[1];
                        int nSequnce = Convert.ToInt32(strRefNo);
                        strResult = bgm_year.ToString() + "/" + (nSequnce + 1).ToString("0000");
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return strResult;
        }

        public string GetNextBorrowNo(Nullable<int> bgm_year, Nullable<int> bgm_source_seq, string use_advance)
        {
            string strSQL;
            DataTable dtb = null;
            string strRefNo = "";
            string strResult = bgm_year.ToString() + "/0001"; ;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@bgm_year", bgm_year);
                dbManager.AddParameters(3, "@use_advance", use_advance);

                strSQL = "SELECT mr.bgm_source_seq, mr.bgm_year, mr.use_advance, max(mr.borrow_no) as ref_no"
                    + " FROM fim_money_requests mr"
                    + " WHERE mr.record_status not like @delete_status"
                    + (bgm_source_seq != null ? " and mr.bgm_source_seq = @bgm_source_seq" : "")
                    + (bgm_year != null ? "" : " and mr.bgm_year = @bgm_year")
                    + (use_advance != null ? "" : " and mr.use_advance = @use_advance")
                    + " group by mr.bgm_source_seq, mr.bgm_year, mr.use_advance";
                

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                if (dtb.Rows.Count > 0)
                {
                    strRefNo = dtb.Rows[0]["ref_no"].ToString();

                    if (strRefNo.Length > 0)
                    {
                        strRefNo = strRefNo.Split('/')[1];
                        int nSequnce = Convert.ToInt32(strRefNo);
                        strResult = bgm_year.ToString() + "/" + (nSequnce + 1).ToString("0000");
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return strResult;
        }

        public void UpdateBorrowReturnDate(int money_req_seq, DateTime real_pay_date)
        {
            string strSQL = "";
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@money_req_seq", money_req_seq);
                dbManager.AddParameters(2, "@real_pay_date", real_pay_date);

                strSQL = "SELECT mr.money_req_seq, bt.return_day"
                    + ", case when bt.cal_date_type = 1"
                        + " then DATEADD(day, coalesce(bt.return_day, 0), @real_pay_date)"
                        + " else mr.borrow_return_date"
                        + " end as borrow_return_date"
                    + " FROM fim_money_requests mr"
                    + " LEFT JOIN fim_mas_borrow_types bt on mr.borrow_type_seq = bt.borrow_type_seq"
                    + " WHERE mr.money_req_seq = @money_req_seq";

                var dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                if (dtb.Rows.Count > 0)
                {
                    dbManager.AddParameters(2, "@real_pay_date", Convert.ToDateTime(dtb.Rows[0]["borrow_return_date"]));

                    strSQL = "update fim_money_requests"
                        + " set borrow_return_date = @real_pay_date"
                        + " WHERE money_req_seq = @money_req_seq";
                    dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
